455

How do I escape the underscore character?

I am writing something like the following where clause and want to be able to find actual entries with _d at the end.

Where Username Like '%_d'
brett rogers
  • 6,501
  • 7
  • 33
  • 43
GateKiller
  • 74,180
  • 73
  • 171
  • 204

8 Answers8

706

T-SQL Reference for LIKE:

You can use the wildcard pattern matching characters as literal characters. To use a wildcard character as a literal character, enclose the wildcard character in brackets. The following table shows several examples of using the LIKE keyword and the [ ] wildcard characters.

For your case:

... LIKE '%[_]d'
Pang
  • 9,564
  • 146
  • 81
  • 122
Lasse V. Karlsen
  • 380,855
  • 102
  • 628
  • 825
258

Obviously @Lasse solution is right, but there's another way to solve your problem: T-SQL operator LIKE defines the optional ESCAPE clause, that lets you declare a character which will escape the next character into the pattern.

For your case, the following WHERE clauses are equivalent:

WHERE username LIKE '%[_]d';            -- @Lasse solution
WHERE username LIKE '%$_d' ESCAPE '$';
WHERE username LIKE '%^_d' ESCAPE '^';
Gerardo Lima
  • 6,467
  • 3
  • 31
  • 47
  • 70
    To complete the picture: the `ESCAPE` clause is part of the SQL standard and will work on any DBMS, not just SQL Server. –  Jun 17 '15 at 07:46
9

These solutions totally make sense. Unfortunately, neither worked for me as expected. Instead of trying to hassle with it, I went with a work around:

select *
from information_schema.columns 
where replace(table_name,'_','!') not like '%!%'
order by table_name
Dale K
  • 25,246
  • 15
  • 42
  • 71
Tek Mailer
  • 91
  • 1
  • 3
8

Adding [ ] did the job for me

like '%[\\_]%'
Dale K
  • 25,246
  • 15
  • 42
  • 71
Alex C.
  • 137
  • 2
  • 6
7

I had a similar issue using like pattern '%_%' did not work - as the question indicates :-)

Using '%\_%' did not work either as this first \ is interpreted "before the like".

Using '%\\_%' works. The \\ (double backslash) is first converted to single \ (backslash) and then used in the like pattern.

Floern
  • 33,559
  • 24
  • 104
  • 119
Freddy Madsen
  • 79
  • 1
  • 2
4

This worked for me, just use the escape '%\_%'

Arnór Barkarson
  • 65
  • 2
3

Adding to Gerardo Lima's answer, I was having problems when trying to use backslash as my escape character for the ESCAPE clause. This caused issues:

SELECT * FROM table WHERE email LIKE '%@%\_%' ESCAPE '\'

It was resolved by switching to an exclamation point. This worked:

SELECT * FROM table WHERE email LIKE '%@%!_%' ESCAPE '!'
Zosoled
  • 31
  • 4
1

None of these worked for me in SSIS v18.0, so I would up doing something like this:

WHERE CHARINDEX('_', thingyoursearching) < 1

..where I am trying to ignore strings with an underscore in them. If you want to find things that have an underscore, just flip it around:

WHERE CHARINDEX('_', thingyoursearching) > 0

wolverine87
  • 11
  • 3