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 related to
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'
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 '^';
This worked for me, just use the escape
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
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
I had a similar issue using like pattern
'%_%' did not work - as the question indicates :-)
'%\_%' did not work either as this first
\ is interpreted "before the like".
'%\\_%' works. The
\\ (double backslash) is first converted to single
\ (backslash) and then used in the like pattern.
Add [ ] did the job for me