SQL LIKE Operator

The LIKE operators are often used in the WHERE clause to search for a pattern specified with “LIKE”  in a column. There are some conjectures that are used with the LIKE operators.

These wildcards are used with LIKE Operators in different combinations.
  • Syntax of LIKE Operator – 

SELECT column1,column2,column3,...,columnN FROM table_name WHERE columnN LIKE pattern ; Let’s see the purpose of each wildcard with the LIKE Operator demonstrated with an example- Demo Table – 

Product Table

Category Region City Country Customer_ID Customer_name
Office Supplies Central Chicago United States SM-20950 Suzanne McNair
Technology East New York City United States AH-10465 Amy Hunt
Office Supplies East New York City United States AH-10465 Amy Hunt
Office Supplies East Dover United States EP-13915 Emily Phan
Office Supplies East Dover United States EP-13915 Emily Phan
Technology West Aurora United States TP-21565 Tracy Poddar
Office Supplies West Aurora United States TP-21565 Tracy Poddar
Office Supplies East Long Beach United States AR-10825 Anthony Rawles
Office Supplies West Pasadena United States HA-14920 Helen Andreada
Furniture West Pasadena United States HA-14920 Helen Andreada

Wildcard ( % ) with LIKE Operator 

Example – Consider the Product table of the Customer database where we want to get those customer names whose name starts with “a”.

SELECT Category,Region,City,[Country/Region],[Customer ID],[Customer Name] FROM [dbo].[Products] WHERE [Customer Name] LIKE 'A%'; This output is the small section of the output by executing the above query –

Category Region City Country Customer_ID Customer_name
Technology East New York City United States AH-10465 Amy Hunt
Office Supplies East New York City United States AH-10465 Amy Hunt

Wildcard ( _ ) with LIKE Operator 

Example – Consider the Product table of the Customer database where we want to get those customer names whose name “d” as their second letter and “r” as their third letter.

SELECT [Customer Name],City,[Country/Region] FROM [dbo].[Products] WHERE [Customer Name] LIKE '_uz'; This output is the small section of the output by executing the above query –

Category Region City Country Customer_ID Customer_name
Office Supplies Central Chicago United States SM-20950 Suzanne McNair

Wildcard ( [characters] ) with LIKE Operator 

Example – Consider the Product table of the Customer database where we want to get those customer names whose name starts with either  “a” or “y”.

SELECT [Customer Name],City,[Country/Region] FROM [dbo].[Products] WHERE [Customer Name] LIKE '[AE]%';

This output is the small section of the output by executing the above query –

Category Region City Country Customer_ID Customer_name
Technology East New York City United States AH-10465 Amy Hunt
Office Supplies East New York City United States AH-10465 Amy Hunt
Office Supplies East Dover United States EP-13915 Emily Phan

Wildcard ( [^ characters] ) with LIKE Operator 

Example – Consider the Product table of the Customer database where we want to get those customer names whose names do not start with either  “a” or “y”.

SELECT [Customer Name],City,[Country/Region] FROM [dbo].[Products] WHERE [Customer Name] LIKE '[^AE]%'; This output is the small section of the output by executing the above query –

Category Region City Country Customer_ID Customer_name
Office Supplies Central Chicago United States SM-20950 Suzanne McNair
Technology West Aurora United States TP-21565 Tracy Poddar
Office Supplies West Aurora United States TP-21565 Tracy Poddar
Office Supplies West Pasadena United States HA-14920 Helen Andreada
Furniture West Pasadena United States HA-14920 Helen Andreada

Other SQL topics to check out:

Follow us on TwitterFacebook, Linkedin, and Tableau Public to stay updated with our latest blog and what’s new in Tableau.

If you are looking forward to getting your data pipeline built and setting up the dashboard for business intelligence, book a call now from here.

#analytics #data #business #artificialintelligence #machinelearning #startup #deeplearning #deeplearning #datascience #ai #growth #dataanalytics #india #datascientist #powerbi #dataanalysis #tableau #SQL #businessanalytics #businessanalyst #businessandmanagement #dataanalyst #businessanalysis #analyst #analysis #powerbideveloper #powerbidesktop #letsviz