pattern matching - SQL select where column begins with

Question

I'm trying to find all the data in ColumnX where the Data begins with a .
Is like '\%' what I'm looking for? But the has to be at the beginning.

Answer 1

The backslash can have special meaning in LIKE patterns. It's the default ESCAPE character in PostgreSQL or MySQL - but not in Oracle or SQL Server. (Can be modified with an added ESCAPE clause in subtly varying ways, follow link for your RDBMS.)

In addition to that, PostgreSQL used to interpret POSIX-style escapes in strings (a.k.a. "C escape syntax") before version 9.1 and MySQL still does in version 8.0). There you have to double to \ to get an ordinary backslash.

According to standard SQL, is not a meta character in strings. PostgreSQL eventually switched to standard behavior and introduced a special escape-string-syntax with E'' and the config settings standard_conforming_strings and escape_string_warning to still allow escaped string when needed.

Since Postgres 9.1 standard_conforming_strings = on by default. So you write:

... col LIKE '\%'    -- double once to get 1 literal backslash in LIKE pattern

Else you have to write:

... col LIKE E'\\%'  -- double twice to also disable special meaning as escape char

---

On top of that, also has special meaning in many client programs and programming languages. If strings get interpreted before they even reach the database engine, you may have to double the another time (or escape it in some other fashion). See this related question, for instance.