RSS

What you don’t know about SQL ‘LIKE’ operator !!

24 Oct

What is the ‘LIKE‘ operator ??

Determines whether a specific character string matches a specified pattern. A pattern can include regular characters and wildcard characters. During pattern matching, regular characters must exactly match the characters specified in the character string. However, wildcard characters can be matched with arbitrary fragments of the character string. Using wildcard characters makes the LIKE operator more flexible than using the = and != string comparison operators. If any one of the arguments is not of character string data type, the SQL Server Database Engine converts it to character string data type, if it is possible.

The new about ‘LIKE‘ .. !!

I will discuss the new usages of LIKE operator, I do not mean that is new in MS SQL Server but I mean that is may be new for you … These features listed as following:-

  • List of values.
  • Exclusion of a list or range of values.
  • Range of values.

Execute the following T-SQL script to create the table and insert data:

CREATE TABLE [dbo].[Customers](
[ID] [int] NOT NULL,
[Name] [varchar](50) NULL,
[Address] [varchar](50) NULL,
CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

————————————-

INSERT INTO [Customers] VALUES (1,  ‘Ahmed Negm’, ‘EGYPT: Cairo’);
INSERT INTO [Customers] VALUES (2,  ‘Islam Othman’, ‘EGYPT: Cairo’);
INSERT INTO [Customers] VALUES (3,  ‘Muhammad Ibrahim’, ‘KSA: Ehsaa”’);
INSERT INTO [Customers] VALUES (4,  ‘Amien Ewadah’, ‘Kuwit: Kuwit’);
INSERT INTO [Customers] VALUES (5,  ‘Youssuf Ali’, ‘EGYPT: Sohag’);
INSERT INTO [Customers] VALUES (6,  ‘May Abdul Rahman’, ‘EGYPT: Fayoum’);
INSERT INTO [Customers] VALUES (7,  ‘Hana Negm’, ‘@ My Heart’);
INSERT INTO [Customers] VALUES (8,  ‘Mahmoud Hussin’, ‘EGYPT: Beni-Suef’);
INSERT INTO [Customers] VALUES (9,  ‘Khalid Muhammad’, ‘EGYPT: Zagazig’);
INSERT INTO [Customers] VALUES (10, ‘Osama Samir’, ‘EGYPT: Minya’);
INSERT INTO [Customers] VALUES (11, ‘Ahmed Saied’, ‘EGYPT: Minya’);
INSERT INTO [Customers] VALUES (12, ‘Abdul Allah Fawzy’, ‘EGYPT: Alex’);

1. List of values.

If we want to search for a list of values, we can search using square brackets. In this example, we want to select all those rows which have ‘H‘,’O‘,’A‘ or ‘K‘ as data in Name column. Use the following T-SQL query:-

SELECT * FROM dbo.Customers WHERE [Name] LIKE ‘[HAOK]%’

/*
1      Ahmed Negm               EGYPT: Cairo
4      Amien Ewadah            Kuwit: Kuwit
7      Hana Negm                   @ My Heart
9      Khalid Muhammad    EGYPT: Zagazig
10    Osama Samir                EGYPT: Minya
11    Ahmed Saied                EGYPT: Minya
12    Abdul Allah Fawzy    EGYPT: Alex
*/

2. Range of values.

Like in regular expression, if we want to search based on a sequence, we use bracket to specify the sequence. e.g. [1-5] means all digits between 1 and 5 inclusive or [a-z] means all characters between ‘a’ and ‘z’. Do you know that the same expressions can be used in SQL Server like operator. Use the following T-SQL query:-

SELECT * FROM dbo.Customers WHERE [Name] LIKE ‘[A-H]%’

/*

1      Ahmed Negm               EGYPT: Cairo
4      Amien Ewadah            Kuwit: Kuwit
7      Hana Negm                   @ My Heart
11    Ahmed Saied                EGYPT: Minya
12    Abdul Allah Fawzy    EGYPT: Alex

*/

3. Exclusion of a list or range of values.

This is used when we want to exclude any set of character or range of character. In this example, we are excluding those rows for which Name does not start with any character in the range of characters from ‘a‘ to ‘H‘. Use the following T-SQL query:-

SELECT * FROM dbo.Customers WHERE [Name] LIKE‘[^a-h]%’

/*

2      Islam Othman                  EGYPT: Cairo
3      Muhammad Ibrahim    KSA: Ehsaa’
5      Youssuf Ali                       EGYPT: Sohag
6      May Abdul Rahman      EGYPT: Fayoum
8      Mahmoud Hussin           EGYPT: Beni-Suef
9      Khalid Muhammad        EGYPT: Zagazig
10    Osama Samir                    EGYPT: Minya

*/

We can also exclude a list of characters. Like in the example below, we are excluding all rows where value of Name starts with any of ‘a‘,’h‘ or ‘m‘. Use the following T-SQL query:-

SELECT * FROM dbo.Customers WHERE [Name] LIKE ‘[^ahm]%’

/*

2      Islam Othman              EGYPT: Cairo
5      Youssuf Ali                   EGYPT: Sohag
9      Khalid Muhammad    EGYPT: Zagazig
10    Osama Samir                EGYPT: Minya

*/

You can see more on LIKE operator in MSDN

 
Leave a comment

Posted by on October 24, 2011 in MS SQL Server

 

Tags: , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: