Bug #24287 <text> LIKE <text> ESCAPE '|'
Submitted: 14 Nov 2006 7:11 Modified: 14 Nov 2006 10:12
Reporter: D R Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.17-nt-max-log, 5.1 BK OS:Windows (WinXP ServicePack 2)
Assigned to: CPU Architecture:Any

[14 Nov 2006 7:11] D R
Description:
<text> LIKE <text> ESCAPE '|'

Following the documentation, I am assuming that using ESCAPE in a like replace the escape caracters for the given query. Yet, I can't get my last exemple in the how to repeat box to work using that tag. Does ESCAPE show the rigth behaviour here?

From documentation (version 5.0):
To test for literal instances of a wildcard character, precede it by the escape character. If you do not specify the ESCAPE character, ‘\’ is assumed. 

String Description 
\% Matches one ‘%’ character 
\_ Matches one ‘_’ character 

mysql> SELECT 'David!' LIKE 'David\_';
        -> 0
mysql> SELECT 'David_' LIKE 'David\_';
        -> 1
To specify a different escape character, use the ESCAPE clause: 

mysql> SELECT 'David_' LIKE 'David|_' ESCAPE '|';
        -> 1
The escape sequence should be empty or one character long. As of MySQL 5.0.16, if the NO_BACKSLASH_ESCAPES SQL mode is enabled, the sequence cannot be empty. 

How to repeat:
DROP TABLE IF EXISTS `test`.`SlashTest`;
CREATE TABLE `test`.`SlashTest` (
  `ID` INTEGER UNSIGNED NOT NULL DEFAULT NULL AUTO_INCREMENT,
  `Text` VARCHAR(45) NOT NULL,
  PRIMARY KEY(`ID`)
)
ENGINE = InnoDB;

INSERT INTO SlashTest(TEXT) VALUES("Allo");
INSERT INTO SlashTest(TEXT) VALUES("Al\lo");
INSERT INTO SlashTest(TEXT) VALUES("Al\\lo");
INSERT INTO SlashTest(TEXT) VALUES("Al\\\lo");
INSERT INTO SlashTest(TEXT) VALUES("\A\l\l\o");

# trying to get "Allo"
SELECT COUNT(*)
FROM SlashTest S
WHERE Text like 'Allo';
# result 3, normal

# trying to get "Allo"
SELECT COUNT(*)
FROM SlashTest S
WHERE Text like 'Al\lo';
# result 3, normal

# trying to get "Al\lo" only, should return 2
SELECT COUNT(*)
FROM SlashTest S
WHERE Text like 'Al\lo' ESCAPE "|";
# result 3, problem? Shouldn't <ESCAPE "|"> replace
# the default espace by the new one for that query

Suggested fix:
See in how to repeat
[14 Nov 2006 10:12] Sveta Smirnova
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Read Note in description of LIKE at http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html