Select Query Using Like Clause in SQL Server 2012

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:

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

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

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

 

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