SQL LIKE to find either -,/,_

Question

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

What RDBMS are you using? You may have to use `REGEX` instead of `LIKE`. — Barmar, May 29 '14 at 17:36
Depending on what database you're using there are usually regular expression versions of LIKE available. MySQL has `RLIKE`, Oracle has `REGEXP_LIKE`, etc. — Mr. Llama, May 29 '14 at 17:36

score 1 · Answer 1 · answered May 29 '14 at 17:59

1

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 '%/%/%'

answered May 29 '14 at 17:59

alzaimar

4,572
1
16
30

score 1 · Answer 2 · answered May 29 '14 at 18:04

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.

score 0 · Answer 3 · edited May 23 '17 at 10:26

0

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.

edited May 23 '17 at 10:26

Community

1
1

answered May 29 '14 at 17:45

Tin Wizard

242
2
16

Doesn't work either, tried both with and without the wildcard _ ([/-] and [/-_]). – Arger May 29 '14 at 17:50
Edited to account for the use of SQLite. – Tin Wizard May 29 '14 at 17:58

score 0 · Answer 4 · answered May 29 '14 at 18:05

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 '.*[/_-].*[/_-].*'

SQL LIKE to find either -,/,_

4 Answers4