SQL Server – SELECT Statement with WHERE LIKE clause

SELECT Statement with WHERE LIKE clause

It often a case when we don’t have an exact matching string need for comparison operations, but we do have some kind of pattern in our mind. In many cases we want the strings to be searched for some particular patterns. In such situations WHERE LIKE clause is very useful.

The LIKE search condition uses wildcard to search for patterns within a string. The wildcards used in SQL are listed in table.

Table: SQL Wildcards

Description SQL Wildcard Example
Multiple characters % ‘Able’ LIKE ‘A%’
Single character _ ‘Able’ LIKE ‘Abl_’
Match in range of characters [ ] ‘a’ LIKE ‘[a-g]’
Match not in range of characters [^ ] ‘a’ LIKE ‘[^w-z]’

Examples:

USE AdventureWorks

— To display all the records whose name starts with ‘c’

SELECT * FROM Production.ProductModel WHERE Name LIKE ‘c%’

— To display all the records whose name starts with ‘c’ and third char. is ‘a’

SELECT * FROM Production.ProductModel WHERE Name LIKE ‘c_a%’

— To display all the records whose name starts with ‘a’,’b’ and ‘c’

SELECT * FROM Production.ProductModel WHERE Name LIKE ‘[a-c]%’

— To display all the records whose name does not starts with ‘a’,’b’, ‘c’ and ‘d’

SELECT * FROM Production.ProductModel WHERE Name LIKE ‘[^a-d]%’

What if the pattern you are searching contains wildcard? There are two methods to resolve this conflict.

Method 1

Enclose the wildcard in square brackets.

USE AdventureWorks

— To display all the records whose name contains pattern ‘full-f’

SELECT * FROM Production.ProductModel WHERE Name LIKE ‘%full[-]f%’

Method 2

Put an escape character before the wildcard. You need to specify the escape character.

USE AdventureWorks

— To display all the records whose name contains pattern ‘full-f’

SELECT * FROM Production.ProductModel WHERE Name LIKE ‘%full&-f%’ ESCAPE ‘&’

See also:

DML SELECT Statement

SELECT Statement with WHERE clause

SELECT Statement with WHERE BETWEEN clause

SELECT Statement with WHERE IN clause

Advertisements

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: