Like In SQL - sql - sql tutorial - learn sql




  • The LIKE operator is used to filter the result set based on a string pattern. It is always used in the WHERE clause. The SQL LIKE clause is used to compare a value to similar values using wildcard operators. There are two wildcards used in conjunction with the LIKE operator.
    • The percent sign (%)
    • The underscore (_)
  • The percent sign represents zero, one or multiple characters.
  • The underscore represents a single number or character.
  • These symbols can be used in combinations.
SQL Like
Tags : sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

Syntax

  • The syntax for the LIKE operator is as follows:
SELECT "column_name"
FROM "table_name"
WHERE "column_name" LIKE {PATTERN};
  • {PATTERN} often consists of wildcards. We saw several examples of wildcard matching in the previous section.
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

Example

  • We use the following table for our example.

Table Store_Information

Store_Name Sales Txn_Date
LOS ANGELES 1500 Jan-05-1999
SAN DIEGO 250 Jan-07-1999
SAN FRANCISCO 300 Jan-08-1999
BOSTON 700 Jan-08-1999
  • We want to find all stores whose name contains 'AN'. To do so, we key in,
SELECT *
FROM Store_Information
WHERE Store_Name LIKE '%AN%';

Result:

Store_Name Sales Txn_Date
LOS ANGELES 1500 Jan-05-1999
SAN DIEGO 250 Jan-07-1999
SAN FRANCISCO 300 Jan-08-1999
  • The "%" sign before 'AN' means that there may be 0, 1, or more characters before the pattern 'AN.'
  • The "%" sign after 'AN' means that there may be 0, 1, or more characters after the pattern 'AN.'
  • Out of the four store names, 'LOS ANGELES,' 'SAN DIEGO,' and 'SAN FRANCISCO' all contain this pattern.
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

Interview for LIKE COMMAND IN SQL

  • For these exercises, assume we have a table called User_Sales with the following data:

Table User_Sales

First_Name Last_Name Birth_Date Gender Join_Date Total_Sales
Sophie Lee Jan-05-1960 F Apr-05-2015 500
Richard Brown Jan-07-1975 M Apr-05-2015 200
Jamal Santo Oct-08-1983 M Apr-09-2015 350
Casey Healy Sep-20-1969 M Apr-09-2015 80
Jill Wilkes Nov-20-1979 F Apr-15-2015 210
  • Which of the following SQL statement is valid? (There can be more than one answer)
  • a) SELECT First_Name, Last_Name FROM User_Sales WHERE First_Name LIKE 'A%' Last_Name LIKE 'W%';
  • b) SELECT First_Name, Last_Name FROM User_Sales WHERE First_Name LIKE 'J%' AND Last_Name LIKE 'W%';
  • c) SELECT First_Name, Last_Name FROM User_Sales First_Name LIKE 'J%' AND Last_Name LIKE 'W%';
  • d) SELECT First_Name, Last_Name FROM User_Sales WHERE First_Name LIKE 'J%', Last_Name LIKE 'W%';
  • Answer: b
  • How many records will be returned by the following query? (Assuming the database is configured to be case-insensitive)
SELECT * FROM User_Sales WHERE Last_Name LIKE '%l_e%';
  • Answer: 2 records are returned. They are,
First_Name Last_Name Birth_Date Gender Join_Date Total_Sales
Sophie Lee Jan-05-1960 F Apr-05-2015 500
Jill Wilkes Nov-20-1979 F Apr-15-2015 210
  • How many records will be returned by the following query? (Assuming the database is configured to be case-insensitive)
SELECT * FROM User_Sales WHERE First_Name LIKE '%a%' OR Last_Name LIKE '%e%';
  • Answer: 5 records are returned. They are,
First_Name Last_Name Birth_Date Gender Join_Date Total_Sales
Sophie Lee Jan-05-1960 F Apr-05-2015 500
Richard Brown Jan-07-1975 M Apr-05-2015 200
Jamal Santo Oct-08-1983 M Apr-09-2015 350
Casey Healy Sep-20-1969 M Apr-09-2015 80
Jill Wilkes Nov-20-1979 F Apr-15-2015 210
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

SQL Like Sample Application

SQL Like Sample Application

This tutorial provides more the basic needs and informations on sql tutorial , pl sql tutorial , mysql tutorial , sql server , sqlcode , sql queries , sql , sql formatter , sql join , w3schools sql , oracle tutorial , mysql , pl sql , learn sql , sql tutorial for beginners , sql server tutorial , sql query tutorial , oracle sql tutorial , t sql tutorial , ms sql tutorial , database tutorial , sql tutorial point , oracle pl sql tutorial , oracle database tutorial , oracle tutorial for beginners , ms sql server tutorial , sql tutorial pdf

Related Searches to Like In SQL