CODEWITHSUNDEEP
mysql
PrimuS
PrimuS

Reputation: 2683

How to SELECT w/ LIKE and a '_' delimiter

I have a table with a name field that can have values like:

CHECK_5_20170909
CHECK_1_20170809
CHECK_11_20170809
CHECK_11_20170909

I would now like to query all fields that have a _1_ in the name, but ONLY them.

I tried this: SELECT * FROM tblName WHERE name LIKE '%_1_%'; but that shows me _11_ AND _1_ in my results.

When I try it with CHECKWHATEVER1WHATEVER20170909 and LIKE %WHATEVER1WHATEVER% it works, are there any special rules for _ in a MySQL Query?

Changing it to another delimiter in the MySQL DB would create a hell of work, is there any "workaround"?

Upvotes: 0

Views: 337

Answers (2)

denny
denny

Reputation: 2254

try this using REGEXP

SELECT * FROM tblName WHERE name regexp '_1_';

it will return exact matches record from column for more reference read here

Upvotes: 0

AYR
AYR

Reputation: 1179

You need to add a '\' before each underscore, otherwise its interpreted as a random "wildcard" character. 

select * from 
(
    select 'CHECK_5_20170909' col 
    union 
    select 'CHECK_1_20170809' 
    union 
    select 'CHECK_11_20170809' 
    union 
    select 'CHECK_11_20170909' 
) t
where col like '%\_1\_%'

Upvotes: 1

Related Questions