Gaps - how to show values with a specific symbol?

Question

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?

Answer 1

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.

Answer 2

You use the "E" before the string to escape the symbol.

SELECT col1
FROM table1
WHERE col1 LIKE E'%\_';