SQL Like operator example

Sql like query is used when we want to find similar records instead of exact match, like search engine internally use like query, as a result, when we search something we get lots of similar records even though there is no exact matching record.

We use wildcard characters with like clause with where clause for finding data from sql table or view, here is how the query structure look like.

SELECT columnNames
      FROM tableName
     WHERE columnName LIKE value 

In the database query we use wildcard characters in 'value', they are % (percent) and _ (underscore).

SQL Where LIKE with wildcard characters

Let's see some examples of how differently we can use like operator in sql query.

Normally we can use like operator in three different ways , like start with, end with and search from both side.

Following query will search all the client where first name start with character 'r'.

select * from [dbo].[tbClientOld] where Firstname like 'r%' 
    

Another example, following query will search all the client where last name end with character 'a'.

select * from [dbo].[tbClientOld] where Lastname like '%a'

Now we how to search from both side, the following query will fetch all the records where last name either start with or end with the specified characters.

select * from [dbo].[tbClientOld] where Lastname like '%a%'

Here is how you can use except in sql query with like and where statement.

select * from [dbo].[tbClientOld] where Lastname like '%a%' 
except select * from [dbo].[tbClientOld] where Firstname like 'Anu'

Now instead of specifying one single character in like statement we also can specify a range of character, if any character match within that range the that record will appear in result.

SQL like query with range of characters

Find any character within range using [a-c] , the following query will find all records where last name ends with any character within range a to f.

select * from [dbo].[tbClientOld] where Lastname like '%[a-f]' 

Now we see how to write SQL query like statement with underscore, what is the use of underscore _ in sql like statement

Finds any values in last name column, that starts with "m" and there are at least 3 characters in length using 'm__%'

select * from [dbo].[tbClientOld] where Lastname like  'm__%'  

There are many wildcard characters in SQL, we often use those characters with where clause in sql query.

• ^ represents any character not in the brackets

  select * from [dbo].[tbClient] where firstname like 'ra[^j]a%'
  

  Above query will find all first name from table which are start with “ra” and end with “a and any character”, except those names, where middle letters contain “j”.