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.

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

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

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

The result table will show the following information:

 

 

Like with Underscore (_)

 

It is used to search for a single character.

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

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

SELECT * FROM UserDetail WHERE FirstName LIKE '__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:

 

Like with Bracket ([])

 

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

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

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

SELECT * FROM UserDetail WHERE FirstName LIKE '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:

 

 

Like with Caret (^)

 

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

SELECT * FROM UserDetail WHERE FirstName LIKE '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 * FROM UserDetail WHERE FirstName LIKE '%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: