SQL Server Escape an Underscore

371

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'

This question is tagged with sql-server

~ Asked on 2008-08-08 11:56:36

7 Answers


583

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'

~ Answered on 2008-08-08 11:59:36


214

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 '^';

~ Answered on 2012-05-02 14:20:40


4

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

~ Answered on 2018-06-01 19:50:03


2

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

~ Answered on 2018-10-08 12:59:46


1

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.

~ Answered on 2020-10-01 10:14:28


0

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

~ Answered on 2019-06-28 18:58:42


-1

like '%[\_]%'

Add [ ] did the job for me

~ Answered on 2021-02-05 19:26:34


Most Viewed Questions: