We use the LIKE operator with WHERE clause. I suggest you read the following article about WHERE clause.
“Where Clause in SQL Server and SQL Server operators(TSQL)”
We filter data with the LIKE operator by using the special symbols “%”, “_”,”[XY]”,”X-Y”,”[^XY]”,”[^X-Y]” on the WHERE clause .
The subject will be understood more easily through examples.
We create a table like the following and add a few records into it.
|
1 2 3 4 5 6 7 8 9 10 |
USE [TestDB] GO CREATE TABLE [dbo].[MyTable1]( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](50) NULL, [Age] int, [Birthday] datetime ) ON [PRIMARY] GO INSERT INTO [dbo].[MyTable1] VALUES ('Nurullah CAKIR',34,'1985-05-05'),('Faruk ERDEM',27,'1991-10-02') |
Returns the records whose name contains “A” in any where in the [Name] column:
|
1 |
Select * FROM [dbo].[MyTable1] WHERE [Name] Like '%A%' |
Returns the records whose name contains “ER” in any where in the [Name] column:
|
1 |
Select * FROM [dbo].[MyTable1] WHERE [Name] Like '%ER%' |
Returns the records whose [Name] begins with “N”:
|
1 |
Select * FROM [dbo].[MyTable1] WHERE [Name] Like 'N%' |
Returns the records whose [Name] ends with “M”:
|
1 |
Select * FROM [dbo].[MyTable1] WHERE [Name] Like '%M' |
Returns the records where the second character is “U” and the third character is “R”:
|
1 |
Select * FROM [dbo].[MyTable1] WHERE [Name] Like '_UR%' |
Returns the records where the third character is “R”(We used “_” two times.):
|
1 |
Select * FROM [dbo].[MyTable1] WHERE [Name] Like '__R%' |
Returns the records where the third character is “R” and whose [Name] ends with “M”:
|
1 |
Select * FROM [dbo].[MyTable1] WHERE [Name] Like '__R%M' |
Returns the records whose [Name] begins with “N” or “F”:
|
1 |
Select * FROM [dbo].[MyTable1] WHERE [Name] Like '[NF]%' |
Returns the records whose [Name] begins in the range “A” and “H”:
|
1 |
Select * FROM [dbo].[MyTable1] WHERE [Name] Like '[A-H]%' |
Returns the records whose [Name] not begins with “N” or “F”:
|
1 |
Select * FROM [dbo].[MyTable1] WHERE [Name] Like '[^AF]%' |
Returns the records whose [Name] not begins in the range “A” and “H”:
|
1 |
Select * FROM [dbo].[MyTable1] WHERE [Name] Like '[^A-H]%' |
NOT LIKE Operator:
Returns the records whose [Name] not begins with “N”:
|
1 |
Select * FROM [dbo].[MyTable1] WHERE [Name] NOT Like 'N%' |
Like Operator in INT Data Types:
We can filter INT data type columns with the LIKE operator. Let’s filter the Age column.
Returns the records whose age contains “4” in any where in the [Age] column:
|
1 |
Select * FROM [dbo].[MyTable1] WHERE [Age] Like '%4%' |
Like Operator in Datetime Data Types:
Like Operator doesnt work correctly in datetime data types. For example the below query should return two rows. But it doesnt return anything.
|
1 |
Select * FROM [dbo].[MyTable1] WHERE [Birthday] Like '19%' |
If you change the query like below, it will return:
|
1 |
Select * FROM [dbo].[MyTable1] WHERE [Birthday] Like '%19%' |
If you want to use Like operator in datetime data types, you should use DATEPART and SUBSTRING functions instead of it.
The below query is equivalent with the first query(WHERE [Birthday] Like ‘19%’)
|
1 |
SELECT * FROM [dbo].[MyTable1] WHERE SUBSTRING(CAST(DATEPART(yy, [Birthday]) AS VARCHAR(4)),1,2) = 19 |
Returns the records whose birth month is “05”:
|
1 |
SELECT * FROM [dbo].[MyTable1] WHERE SUBSTRING(CAST(DATEPART(mm, [Birthday]) AS VARCHAR(2)),1,2) = 05 |
Returns the records whose birth month is “10”:
|
1 |
SELECT * FROM [dbo].[MyTable1] WHERE SUBSTRING(CAST(DATEPART(mm, [Birthday]) AS VARCHAR(2)),1,2) = 10 |
Returns the records whose birth day is “02”:
|
1 |
SELECT * FROM [dbo].[MyTable1] WHERE SUBSTRING(CAST(DATEPART(dd, [Birthday]) AS VARCHAR(2)),1,2) = 02 |
Possible Errors:
If we do not convert the result of the DATEPART function to the varchar data type with CAST, we receive the error as follows.
|
1 |
SELECT * FROM [dbo].[MyTable1] WHERE SUBSTRING(DATEPART(dd, [Birthday]),1,2) = 02 |
Msg 8116, Level 16, State 1, Line 1
Argument data type int is invalid for argument 1 of substring function.
