CODEWITHSUNDEEP
sqlsqlitewildcardsql-like
Arger
Arger

Reputation: 170

SQL LIKE to find either -,/,_

Trying to select from table where the format can be either 1/2/2014, 1-2-2014 or 1_2_2014 in a text field. There's other text involved outside of this format but it shouldn't matter, but that's why this is text not a date type.

I tried '%[-,_,/]%[-,_,/]%' which doesn't work, and I've tried escaping the special characters in the brackets such as %[-,!_,/]%[-,!_,/]%' ESCAPE '!' which also doesn't work. Any suggestions?

I wanted to avoid using three searches like,

LIKE '%/%/%'
OR '%-%-%'
OR '%!_%!_%' ESCAPE '!'

EDIT: Using SQLite3

Upvotes: 1

Views: 149

Answers (4)

Antti Haapala -- Слава Україні
Antti Haapala -- Слава Україні

Reputation: 134066

It is not possible using the LIKE syntax.

However Sqlite3 would support the REGEXP operator; this is syntactic sugar for calling an user defined function that actually does the matching. If provided by your platform, then you could use for example

x REGEXP '.*[/_-].*[/_-].*'

Upvotes: 0

Arger
Arger

Reputation: 170

Thanks for the information. I ended up switching to the GLOB operator which support [] in SQLite.

The Example was altered to GLOB '?[/-_]?[/-_]??*' Where * serves as % and ? serves as _ for the GLOB function.

Also thanks to Amadeaus9 for pointing out minimum characters between delimiters so that '//' isn't a valid answer.

Upvotes: 1

alzaimar
alzaimar

Reputation: 4622

There is no regex like behavior in using the LIKE operator in SQL. You would have use two expressions and OR them together:

select * from table
 where column like '%-%-%'
    or column like '%/%/%'

Upvotes: 1

Tin Wizard
Tin Wizard

Reputation: 242

If you're using T-SQL (AKA SQL Server) you don't want to have commas in the character set - i.e. LIKE '%[/_-]%[/_-]%'. However, keep in mind that this can match ANYTHING that has, anywhere within it, any two characters from the set.

EDIT: it doesn't looke like SQLite supports that sort of use of its LIKE operator, based on this link. Relevant quote: 

There are two wildcards used in conjunction with the LIKE operator:
The percent sign (%)
The underscore (_)

However, you may want to take a look at this question, which details using regex in SQLite.

Upvotes: 0

Related Questions