I have a column with numbers - 1,2,3...100. 5 of them looks like 1_, 3_, 5_ So, actually I have 1, 1_, 2, 3, 3_, 4, 5, 5_, 6, 7, 8 ... I did the query:
SELECT code
FROM table.column1
WHERE column1 LIKE '%_'
to get the list of this numbers with _. Instesd of it I got the list of all values, including values with _. What's your opinion - what's the reason, where is a mistake?
CodePudding user response:
The character _
is, similar to %
, a wildcard in LIKE
-operations. While %
matches multiple characters, _
only matches a single character. To get your expected result you need to escape the _
character:
SELECT code
FROM table.column1
WHERE column1 LIKE '%\_'
Here is a dbfiddle showing it.
CodePudding user response:
You use the "E" before the string to escape the symbol.
SELECT col1 FROM table1 WHERE col1 LIKE E'%_';