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.
Asked
Active
Viewed 2.6k times
6
Erwin Brandstetter
- 605,456
- 145
- 1,078
- 1,228
-
1Does this not work for you `where col like '\%'` ? – Mikael Eriksson Feb 04 '12 at 14:25
-
That should work. If you are querying from a language like java or C# where '\' has special meaning, you may need to escape your query string ('\\%' or @"\%") first – StuartLC Feb 04 '12 at 14:27
4 Answers
11
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.
Erwin Brandstetter
- 605,456
- 145
- 1,078
- 1,228
-
1And in MySQL the backslash has particular significance in `LIKE` patterns as an escape character. – Martin Smith Feb 04 '12 at 14:42
5
Indeed '\%' is what you need. The following statement brings all the data where columnX starts with '\'.
select * from table_name where columnX like '\%'
Korhan Ozturk
- 11,148
- 6
- 36
- 49
-
Maby you have to escape the "\", depending on the database or programming language. – tbraun89 Feb 04 '12 at 14:27
-
I don't think that '\' character needs to be escaped in SQL statements. I have tried for MySQL and Oracle and it worked. – Korhan Ozturk Feb 04 '12 at 14:35
-
@KorhanÖztürk - [Shouldn't have worked in MySQL.](http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html) `\%` Matches one `%` character as `\\` is the pattern escape character. – Martin Smith Feb 04 '12 at 14:38
-
If you run it directly on the database it should work with the most, but when you run it out of a language like java or ruby you have to escape these special characters. In java it would be "\\%" and ruby should be sth. like "\\%%" – tbraun89 Feb 04 '12 at 14:39
-
I've ran it directly on the db, escaping would probably be required otherwise if running through a programming language as @tbraun89 mentioned. – Korhan Ozturk Feb 04 '12 at 14:43
-
@KorhanÖztürk - `\%` is documented to match a percent not a back slash in MySQL see my earlier link. – Martin Smith Feb 04 '12 at 14:46
1
try this...
select * from myTable where ColumnX like '\%'
Good Luck!!!
Fahim Parkar
- 30,974
- 45
- 160
- 276
