ESCAPE character issue
Author: Damien9222Created Oct 2, 2024Updated Sep 10, 2026
LabelsHelp wantedGood first issue
Hi,
Using the ESCAPE character in LIKE clause for 2 columns do not filter anything. To have the LIKE clause correctly applied, we must change the ESCAPE character (for example using ^).
- Replicate the problem in jsfiddle using:
alasql("CREATE TABLE cities (city string, population string)");
alasql("INSERT INTO cities VALUES ('%','1'), ('1%','2'), ('%1','3'), ('1%1','4'), ('_1','5'), ('1^1','6'), ('1!1','7')");
// Case 1 : backslash ESCAPE char:
/* OK: var res = alasql("SELECT * FROM cities WHERE (city LIKE '%^%%' ESCAPE '^') OR (population LIKE '2^%' ESCAPE '^') ORDER BY population DESC"); // =>returns 5 members */
/* KO: var res = alasql("SELECT * FROM cities WHERE (city LIKE '%\\%%' ESCAPE '\\') OR (population LIKE '2\\%' ESCAPE '\\') ORDER BY population DESC"); // => no filter */
showResult(res)_
function showResult(x){
document.getElementById('result').textContent = JSON.stringify(x, null, '\t');
}A second point about escaping (no jsfiddle because not a possible to use double quote without escaping it with a backslash!): Adding in the table a member with double quote char (for example: "1 in cities), and searching the double quote char, using the ^ for ESCAPE, executing the following SQL :
'SELECT * FROM cities WHERE (city LIKE "%^"%" ESCAPE "^" OR population LIKE "%^"%" ESCAPE "^") ORDER BY population DESC"'return a parsing error: "SyntaxError: Parse error on line 1:..."
Modifying the ESCAPE char to use :
'SELECT * FROM ms_inmemory_member_provider_database_id_0.defaultTable WHERE (name LIKE "%\\"%" OR description LIKE "%\\"%") ORDER BY sortingKey ASC LIMIT 150 OFFSET 0'returns correct result. Thanks a lot! Regards.
Source: AlaSQL/alasql