Like Operator in SQL Server(TSQL)

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.

Returns the records whose name contains “A” in any where in the [Name] column:

Returns the records whose name contains “ER” in any where in the [Name] column:

Returns the records whose [Name] begins with “N”:

Returns the records whose [Name] ends with “M”:

Returns the records where the second character is “U” and the third character is “R”:

Returns the records where the third character is “R”(We used “_” two times.):

Returns the records where the third character is “R” and whose [Name] ends with “M”:

Returns the records whose [Name] begins with “N” or “F”:

Returns the records whose [Name] begins in the range “A” and “H”:

Returns the records whose [Name] not begins with “N” or “F”:

Returns the records whose [Name] not begins in the range “A” and “H”:

NOT LIKE Operator:

Returns the records whose [Name] not begins with “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:

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.

If you change the query like below, it will return:

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%’)

Returns the records whose birth month is “05”:

Returns the records whose birth month is “10”:

Returns the records whose birth day is “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.

Msg 8116, Level 16, State 1, Line 1

Argument data type int is invalid for argument 1 of substring function.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *