T-SQL LIKE Operator
In Transact-SQL, The LIKE operator is used to search the pattern specified in the column with WHERE clause. The LIKE clause in Transact-SQL is used to compare the same values to use the wildcard operators.
Two wildcards are used in the combination with LIKE operator:
- % – The percent sign represents zero, one or some characters.
- _ – The underscore sign represents a single character.
Note: MS Access uses the asterisk (*) sign in place of a percent sign (%) and a question mark (?) Instead of underscore (_).
The percent sign represents ‘0’, ‘1’ or more characters in LIKE operator. And the underscore sign represents one character.
Basic syntax of % (Percent Sign) and _ (the underscore) according to the LIKE operator.
|1||SELECT *column-list FROM table_name|
WHERE column LIKE ‘XXXX%.’
|2||SELECT *column-list FROM table_name|
WHERE column LIKE ‘_XXXX.’
|3||SELECT *column-list FROM table_name|
WHERE column LIKE ‘_XXXX_.’
|4||SELECT *column-list FROM table_name|
WHERE column LIKE ‘XXXX_.’
|5||SELECT *column-list FROM table_name|
WHERE column LIKE ‘%XXXX%.’
We bind the conditions of LIKE operator by using the AND or OR operators. “XXXX” is a numeric or string value.
Below are some examples in which we are showing WHERE part having the different LIKE clause with the modules ‘%’ and ‘_’ operators.
|Sr.No||Statement & Description|
|1||WHERE SALARY LIKE ‘300%.’|
It finds any value that starts with 300
|2||WHERE SALARY LIKE ‘%300%.’|
It findsany value that has 300 in any position
|3||WHERE SALARY LIKE ‘_000%.’|
It finds any value that has 000 in the second, third and fourth positions
|4||WHERE SALARY LIKE ‘3_%_%.’|
It finds any value that starts with 3 and have at least 3 characters in length
|5||WHERE SALARY LIKE ‘%4’|
It finds any value that ends with 4
|6||WHERE SALARY LIKE ‘_3%4.’|
It finds any value that has a 3 in the second position and ends with 4
|7||WHERE SALARY LIKE ‘3___4’|
It finds any value in a five-digit number that starts with 3 and ends with 4
Consider the CUSTOMERS table having the following records.
The command is an example, which will display all the records from the CUSTOMERS table where SALARY starts with 200.
The above command will produce the below output.
Below command is the example, which displays all the records from the CUSTOMERS table where SALARY ends with 50.
The command gives the below output.
The given command is an example, which can display all the records from the CUSTOMERS table where SALARY starts with 1 and ends with 0.
The above command gives the below output.