PostgreSQL - how to check if my data contains a backslash

Question

SELECT count(*) FROM table WHERE column ilike '%/%';

gives me the number of values containing "/"

How to do the same for "\"?

Answer 1

Excerpt from the docs:

Note that the backslash already has a special meaning in string literals, so to write a pattern constant that contains a backslash you must write two backslashes in an SQL statement (assuming escape string syntax is used, see Section 4.1.2.1). Thus, writing a pattern that actually matches a literal backslash means writing four backslashes in the statement. You can avoid this by selecting a different escape character with ESCAPE; then a backslash is not special to LIKE anymore. (But it is still special to the string literal parser, so you still need two of them.)

Answer 2

SELECT  count(*)
FROM    table
WHERE   column ILIKE '%\\\\%';

Answer 3

You need E'\\\\' because the argument to LIKE is a regex and regex escape char is already \ (e.g ~ E'\\w' would match any string containing a printable char).

See the doc

Answer 4

better yet - don't use like, just use standard position:

select count(*) from from table where 0 < position( E'\\' in column );