13

I have a table where a column allows special characters like '/' (forward slash) and '' (back slash).

Now when I try to search such records from table, I am unable to get those.

For example: abc\def or abc/def

I am generating a search query like:

select * from table1_1 where column10 like '%abc\def%'

It is returning 0 rows, but actually there is 1 record existing that should be returned. How do I write the query in this case?

TylerH
  • 20,799
  • 66
  • 75
  • 101
JAVAC
  • 1,230
  • 3
  • 17
  • 38
  • 2
    PL/SQL is Oracle's procedural language (not the same as SQL)... MySQL is a completely different RDBMS... are you using Oracle or MySQL? – Ben May 10 '13 at 20:33

6 Answers6

28

The trick is to double escape ONLY the backslash; for string escapes only a single escape is needed.

For example

  • The single quote ' only needs escaping once LIKE '%\'%'
  • But to query backslash \ you need to double escape to LIKE '%\\\\%'
  • If you wanted to query backslash+singlequote \' then LIKE '%\\\\\'%' (with 5 backslashes)

Explanation Source excerpt:

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.

TylerH
  • 20,799
  • 66
  • 75
  • 101
xchiltonx
  • 1,946
  • 3
  • 20
  • 18
  • 1
    actually i made it worked long time back by keep adding unltil query worked.. this explanation what i need so +1 and my answer – JAVAC Feb 05 '15 at 16:39
  • any idea for forward slash?? In insert query. My Text is somex/Somey. And this giving me error always. – Saad Mar 11 '15 at 08:35
  • 1
    you can use $newtext=addslashes('somex/Somey'); function in php. http://php.net/addslashes – xchiltonx Mar 15 '15 at 18:17
  • 2
    This does not explain why you need four slashes. A double escape would mean two slashes, logically, plus the one slash that is actual content, leaving us with `\\\`, not `\\\\`. The fourth `\` appears to be due to the `LIKE` clause being used, based on Barmar's answer, but this answer lacks that information. – TylerH Jan 03 '22 at 22:03
15

In MySQL, this works:

select * from Table1
where column10 like '%abc\\\\def%'

FIDDLE

Backslash is an escape prefix for both strings and LIKE patterns. So you need to double it once for LIKE, and again for string literal syntax.

Barmar
  • 741,623
  • 53
  • 500
  • 612
1

You have to escape the \ with another \

 select * from table1_1 where column10 like '%abc\\def%'
Explosion Pills
  • 188,624
  • 52
  • 326
  • 405
1

Escaping the LIKE value didn't work when I tried it on MySQL v5.5. After a few tries, what did work was a regex (and I had to escape there too, plus hide it in a character class). I finally got it to work like this:

select * from table1_1 where column10 rlike 'abc[\\]def'

These didn't work:

... column10 like '%abc\\def%'
... column10 like concat('%abc', char(92), 'def%')
... column10 rlike 'abc\\def'

Note you also have this tagged as PL/SQL, which is Oracle. The query you posted works as-is on Oracle. Is the PL/SQL tag a mistake?

Ed Gibbs
  • 25,924
  • 4
  • 46
  • 69
0

Use escaping.

https://dev.mysql.com/doc/refman/5.0/en/string-literals.html

Table 9.1. Special Character Escape Sequences

Escape Sequence Character Represented by Sequence \0 An ASCII NUL (0x00) character.

\' A single quote (“'”) character.

\" A double quote (“"”) character.

\b A backspace character.

\n A newline (linefeed) character.

\r A carriage return character.

\t A tab character.

\Z ASCII 26 (Control+Z). See note following the table.

\ A backslash (“\”) character.

\% A “%” character. See note following the table.

_ A “_” character. See note following the table.

Tamas
  • 10,953
  • 13
  • 47
  • 77
0

If you use PHP sprintf() to put together your query where you want:

AND column10 LIKE '%/%'

This worked for me inside sprintf():

AND column10 LIKE '%%/%%'

(MySSQL 8 - WAMP)

Derek Gogol
  • 1,292
  • 16
  • 15