Archive

Posts Tagged ‘LIKE’

SQL Server: Like with Underscores

January 28, 2010 Leave a comment

There are times when you need to do a LIKE based search and need to find an underscore.  I was surprised to find the underscore is reserved as a single character wild card.

There are two ways to get this done.  First is the explicit ESCAPE definition as follows where the backslash is the escape character.

-- looking for anything with an underscore
SELECT  *
FROM    dbo.SomeTable
WHERE   ColumnName like '%\_%' ESCAPE '\'

To me, a better approach is surrounding the character is square brackets.

-- looking for anything with an underscore
SELECT  *
FROM    dbo.SomeTable
WHERE   ColumnName like '%[_]%'

The second approach may get a little confusing when looking for brackets though.

-- Looking for '[' + sometext + ']'
SELECT  *
FROM    dbo.SomeTable
WHERE   ColumnName like '%[[]%[]]%'

Hope this helps.

Advertisements
Categories: Code Tags: , ,