I know the meaning of % and _ wildcard characters ,but i was stuck in a question which was using the two additional characters \% and \\,i was not able to understand what these characters actually mean in the SQL query
SELECT productID FROM productList WHERE productName LIKE 'ab\%cd%' and
SELECT productID FROM productList WHERE productName LIKE 'ab\\cd%' are these two same things or different ??
13 Answers
Since % is a special character, you have to escape it with a \ to match a literal % symbol in your data. So, 'ab\%cd%' matches the letter a, followed by the letter b, followed by a % symbol, the letter c, the letter d, then any other text (because the last % is a wildcard).
Similarly, since \ is a special character used to create escape sequences, you have to escape it to match a literal \ in a pattern, so to match a single \ you have to encode it as \\.
I believe the best way to see the difference is by example.
To better understand it you will need knowledge about 3 things when using LIKE operator in SQL:
\is used to escape special characters to use them as normal chars%is used to match any number of characters (including 0)- special characters are
\and%so if you want to include them literally you need to escape them, so to check for them in text column you respectively need to use\\and\%.
Below is a table with words and true/false results for LIKE comparison with both patterns:
word | ab\%cd% | ab\\cd% ----------+---------+--------- ab\ | f | f -- this would match second pattern but there is no "cd" at the end ab\cd | f | t -- \\ is escaped "\", and % matches none characters ab\cdxzy | f | t -- \\ is escaped "\", and % matches character sequence "xzy" abcd | f | f -- every string requires either "%" or "\" character after "ab" ab%cd | t | f -- \% is escaped "%", and % matches none characters ab%cdxzy | t | f -- \% is escaped "%", and % matches character sequence "xzy" ab\%cd | f | f -- there is no pattern which matches both chars "\%" in sequence ab%\cd | f | f -- same as above, but characters are "%\" in sequence The
\%and\_sequences are used to search for literal instances of%and_in pattern-matching contexts where they would otherwise be interpreted as wildcard characters.
For \\ it searches for a single back slash \.
Ref: MySQL 8.0 Reference Manual, 9.1.1 String Literals, Table 9.1 Special Character Escape Sequences
