CODEWITHSUNDEEP
mysql
Aasif Solkar
Aasif Solkar

Reputation: 81

Search of String value having special character in MySQL

I have one table emp in MySQL database having column as name. In that name column, the value is 'abc\xyz'. I want to search this value. I have tried using following query:

select * from emp where name like 'abc\xyz';

Also i have tried

select * from emp where name like 'abc\xyz' escape '\\';

But i did not found any output. Could you please help me in finding such strings? Such strings can have special character at any location.

Thanks in advance.

Upvotes: 1

Views: 3484

Answers (2)

Rahul Tripathi
Rahul Tripathi

Reputation: 172378

You may try like this:

select * from emp
where empname like '%abc\\\\xyz%'

SQL Fiddle Demo

From the docs:

Because MySQL uses C escape syntax in strings (for example, “\n” to represent a newline character), you must double any “\” that you use in LIKE strings. For example, to search for “\n”, specify it as “\\n”. To search for “\”, specify it as “\\\\”; this is because the backslashes are stripped once by the parser and again when the pattern match is made, leaving a single backslash to be matched against.

Upvotes: 3

Shen Shen
Shen Shen

Reputation: 72

SELECT REPLACE(text,'\\','') FROM tbl

You can use REPLACE to remove some special chars :)

Upvotes: 0

Related Questions