Like in mysql with special character \

Question

My query is SELECT * FROM PMT_PROJECT WHERE PROJECT_CODE LIKE '%A3\7\2011%' AND COMPANY_ID=14. PROJECT_CODE column contains the exact match record but, my above query is unable to fetch the record from MySql db. please do correct my like syntax regarding \ special character, if mistake in above query or else suggest me how to fetch the above record.

possible duplicate of [how to escape special character in mysql](http://stackoverflow.com/questions/881194/how-to-escape-special-character-in-mysql) — ajreal, Sep 08 '11 at 05:58
my query is not gives any error it's unable to fetch record and suppose i change the above is like %A3/7/2011%' and if record is present inside my db then it will fetch the record — subodh, Sep 08 '11 at 06:12

Doug Kress · Accepted Answer · 2013-01-15T20:48:09.160

8

Backslashes are difficult in mysql. I just ran a test directly from the mysql client, and found that to add the field, you do a single escape (two backslashes):

INSERT INTO mytable (field1) VALUES ('ASDFA3\\7\\2011QWER');

But with the LIKE command, escapes are needed to be represented literally, so you need to double-escape the statement (four backslashes):

SELECT * FROM mytable WHERE field1 LIKE '%A3\\\\7\\\\2011%';

Add to that the escapes necessary for you application layer, and it could get quite confusing. An alternative is to use a single character wildcard:

SELECT * FROM mytable WHERE field1 LIKE '%A3_7_2011%';

Another alternative is to use the 'NO_BACKSLASH_ESCAPES' mode:

SET SESSION sql_mode='NO_BACKSLASH_ESCAPES';
SELECT * FROM mytable WHERE field1 LIKE '%A3\7\2011%';

EDIT (thanks, lucek):

Or, to preserve the current sql_mode:

SET @temp_sql_mode = @@SESSION.sql_mode;
SET SESSION sql_mode = CONCAT_WS(',', @@SESSION.sql_mode, 'NO_BACKSLASH_ESCAPES');
-- YOUR_SELECT_GOES_HERE;
SET SESSION sql_mode = @temp_sql_mode;

edited Jan 15 '13 at 20:48

answered Sep 08 '11 at 06:15

Doug Kress

3,537
1
13
19

after executing this SET SESSION sql_mode='NO_BACKSLASH_ESCAPES'; how to set SET SESSION sql_mode default as it was. – subodh Sep 08 '11 at 09:01
Just do a `SET SESSION sql_mode='';` – Doug Kress Sep 08 '11 at 21:42
@DougKress actually it'll mess things up if more than one flag has been set in sql_mode. Proper way of doing this is: SET (at)temp_sql_mode = (at)(at)SESSION.sql_mode; SET SESSION sql_mode = CONCAT_WS(',', (at)(at)SESSION.sql_mode, 'NO_BACKSLASH_ESCAPES'); YOUR_SELECT_GOES_HERE; SET SESSION sql_mode = (at)temp_sql_mode; – matt Jan 15 '13 at 09:24

score 1 · Answer 2 · edited Jul 23 '12 at 07:53

1

The backslash is the standard escape character in MySQL. If you want to use the backslash character literally, you have to define another escape character for this query:

SELECT *
FROM PMT_PROJECT
WHERE PROJECT_CODE LIKE '%A3\7\2011%' ESCAPE '|' AND COMPANY_ID=14

edited Jul 23 '12 at 07:53

Taz

3,718
2
37
59

answered Sep 08 '11 at 06:26

Denis

11
1

I tried to find documentation of "ESCAPE 'character'" but came up with nothing. Can You point me to MySQL documentation with ESCAPE described? – matt Jan 15 '13 at 09:16

Like in mysql with special character \

2 Answers2