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