Home > Code > SQL Server: Like with Underscores

SQL Server: Like with Underscores

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: , ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: