Reader Level:
ARTICLE

Select Query Using Like Clause in SQL Server 2012

Posted by Rohatash Kumar Articles | SQL Server October 19, 2012
In this article we will see how to use the "like" Clause in a SQL Server database.
  • 0
  • 0
  • 13439

The "like" operator is used in a "where" clause to search for a specified pattern of characters using the wildcard mechanism in a column. Wildcard characters make the "like" operator more flexible than using = and != (Not Equal To) string comparison operators. To search for a character string using one or more wildcard characters in a LIKE query, simply include the wildcards in a string literal along with the portion of the string. So let's take a look at a practical example of how to use a like operator to search in SQL Server. The example is developed in SQL Server 2012 using the SQL Server Management Studio.

Wildcard Characters

There are four types of wildcard characters in SQL Server:

  1. Percent sign (%)
  2. Underscore (_)
  3. Bracket ([])
  4. Caret (^)

Creating a Table in SQL Server

The table looks as in the following figure:

Table-in-SQL-Server.jpg

Now we perform searching on the above table using wildcard characters.

Like with Percent sign (%)

It is used to search any string of zero or more characters.

Example

SELECT*FROMUserDetailWHEREFirstNameLIKE'ra%' -- return all columns from UserDetail table where FirstName name start with 'ra'

SELECT*FROMUserDetailWHEREFirstNameLIKE'%n'   -- return all columns from UserDetail table where FirstName name End with 'n'

SELECT*FROMUserDetailWHEREFirstNameLIKE'%an%'-- return all columns from UserDetail table where FirstName name have the letters End with 'an'

 

The result table will show the following information:

Output

Percentage-wildcard-character-in-SQL-Server.jpg

Like with Underscore (_)

It is used to search for a single character.

Example

SELECT*FROMUserDetailWHEREFirstNameLIKE'rah_' -- return all columns from UserDetail table where FirstName name is Four characters long and start with 'rah'

SELECT*FROMUserDetailWHEREFirstNameLIKE'_ahu'   -- return all columns from UserDetail table where FirstName name is Four characters long End with 'ahu'

SELECT*FROMUserDetailWHEREFirstNameLIKE'__e'-- return all columns from UserDetail table where FirstName name start and end with any character whereas the middle character must be 'e'.

 

The result table will show the following information:

 

Output


Underscore-wildcard-character-in-SQL-Server.jpg

Like with Bracket ([])

It is used to search for any single character within the specified range.

Example

SELECT*FROMUserDetailWHEREFirstNameLIKE'[rc]%' -- return all columns from UserDetail table where FirstName name begin with 'R' or 'C'

SELECT*FROMUserDetailWHEREFirstNameLIKE'[rc]%u'  -- return all columns from UserDetail table where FirstName name begin with 'R' or 'C' and end with 'U'.

SELECT*FROMUserDetailWHEREFirstNameLIKE'R[a]%'-- return all columns from UserDetail table where FirstName name begin with 'R' and hold an 'a' in the second place.

 

The result table will show the following information:

 

Output


Bracket-wildcard-Character-in-SQL-Server.jpg

 

Like with Caret (^)

 

It is used to search for any single character that is not the specified character.

 

SELECT*FROMUserDetailWHEREFirstNameLIKE'R[^H]%' -- return all columns from UserDetail table where FirstName name begin with 'R' and does not contain an 'H'  as the second place

SELECT*FROMUserDetailWHEREFirstNameLIKE'%N[^R]'-- return all columns from UserDetail table where FirstName name Contain with 'N' and does not contain an 'R' 

 

The result table will show the following information:

 

Output


Caret-wildcard-character-in-SQL-Server.jpg

COMMENT USING

Trending up