Where like in SQL

Overview: In this tutorial you will understand the LIKE logical operator and where you can use WHERE LIKE in SQL with 6 different examples.

Introduction: like in SQL query 

In SQL Server LIKE is a logical operator that determines if a character string matches a pattern. LIKE operator in SQL query is used in WHERE clause of the SELECT statements to filter rows based on pattern matching.

Like in SQL query also used in UPDATE and DELETE statements to filter rows based on pattern matching.

There are few wildcards often used with the LIKE operator:

• “%” The percent sign represents zero, one, or multiple characters
• “_” The underscore represents a single character
• “[list of alphabets]” any single alphabets within the specified set.
• “[alphabets-alphabets]” any single alphabets within the specified range.
• ” [^]” any single character not within a list or a range.

Syntax:

SELECT 
      column1, column2
FROM 
      table_name
WHERE 
      column LIKE pattern

Example #1: “%” The percent sign end of text with WHERE LIKE

In this below example, Like SQL Query will selects columns as mentioned in SQL query with a Customer name starting with “M”, because we have added “%” wild cards at end of the text/character.

As you can see in Result in column second “Customer_Name” start with only “M”

SELECT 
     [Customer_ID],
     [Customer_Name],
     [Province]
FROM 
     [Customers_Tbl]
WHERE 
     [Customer_Name] LIKE 'M%'

Result:

Example #2: “%” The percent sign Beginning of text

In this below example, Like SQL Query will selects columns as mentioned in SQL query with a Customer name ending with “on”, because we have added “%” wild cards at the beginning of the text/character.

As you can see in Result in column second “Customer_Name” end with only “on”

SELECT
       Customer_Name
FROM
       Customers_Tbl
WHERE
       Customer_Name LIKE '%on'

Result:

Example #3: “%” The percent sign in middle of text

In this below example, Like operator  % wild card is assigned in the middle of two text/character. so this query will extract the records from table only for those customer_name which start with “S“ and end with “Y“

SELECT 
       [Customer_Name],
       [Province]
FROM 
       [dbo].[Customers_Tbl]
WHERE 
       [Customer_Name] LIKE 'S%Y'
GROUP BY
       [Customer_Name],
       [Province]

Result:

Example #4: Use underscore “_” in WHERE LIKE in SQL

The Underscore represent the single character/text/alphabets, it means this wild card will show records with one text in the place of “_”.

In this below example, Like operator  % wild card is used with “_” so that we can see those name of customer which have “A” alphabet in the second position of name and Customer_name end with “Z“

SELECT 
      [Customer_Name],
      [Province]
FROM 
      [dbo].[Customers_Tbl]
WHERE 
      [Customer_Name] LIKE '_A%Z'
GROUP BY
      [Customer_Name],
      [Province]

Result:

Example #5: “[list of alphabets]” Start with Alphabets 

This Bracket is very useful when you want to search in table for more then one Text/Alphabets, In the below example it shows to show all records which start with K and D, You can also assignee more alphabets / Characters

SELECT 
       [Customer_Name],
       [Province]
FROM 
       [dbo].[Customers_Tbl]
WHERE 
       [Customer_Name] LIKE '[KD]%'
GROUP BY
       [Customer_Name],
       [Province]

Result:

Example #6: “[alphabets-alphabets]” the Range 

This Bracket is very useful when you want to search in table for Range of  Text/Alphabets, In the below example it shows to extract records which start with Range of text / Characters from U and Y, meaning it will show Customer_Name start with U, V, W, X, Y

As in Customer table we have only customers within this Range name start with V, X, Y

SELECT 
      [Customer_Name]
FROM 
      [dbo].[Customers_Tbl]
WHERE 
      [Customer_Name] LIKE '[U-Y]%'
GROUP BY
      [Customer_Name]

Result:

Conclusion:

With above 6 example, hope you understand that how you can implement and use LIKE operator with WHERE clause. This will really helpful in data manipulation during analysis of data in SQL server.