Error 1064# While using mysql Like keyword

Question

my sql query is:

mysql> select emp_id where classes like '%'XII'%';

but i am getting this error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where classes like '%'XII'%'' at line 1

I can understand that error is due to single quotes i have used in like arguments but i have string say 'X','XI','XII' in my database. that's why i am querying with single quote because if i query for class X in String 'XI','XII' then it will return emp_id of those who are teaching XI and XII standard which is undesirable.

Can i use escape characters for special characters in like argument.

You forgot `FROM` clause. – krokodilko May 04 '14 at 06:49 — krokodilko, May 04 '14 at 06:49

score 1 · Answer 1 · edited May 23 '17 at 11:59

1

Use double qoutes to enclose search pattern

select emp_id from tableName 
where classes like "%'XII'%";

Please dont store values as comma seprated strings , this is not a good database design practice. See why here , esp Lasse V. Karlsen answer

So instead of

empid   classes
1       'X','XI','XII'

store this as separate rows

empid   classes
1       'X'
1       'XI'
1       'XII'

edited May 23 '17 at 11:59

Community

1
1

answered May 04 '14 at 06:48

Mudassir Hasan

26,910
19
95
126

score 1 · Accepted Answer · answered May 04 '14 at 06:49

1

You can escape a single quote (') by doubling it (''):

select emp_id FROM my_table where classes like '%''XII''%';

Note: These are two single quotes, not a double quote.

answered May 04 '14 at 06:49

Mureinik

277,661
50
283
320

Error 1064# While using mysql Like keyword

2 Answers2