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
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