The MS SQL Server LIKE condition is utilized to contrast an incentive with comparable qualities utilizing special case administrators. There are two special cases utilized related to the LIKE administrator −
- The percent sign (%)
- The underscore (_)
The percent sign speaks to zero, one, or different characters. The underscore speaks to a solitary number or character. The images can be utilized in blends.
Syntax
Following is the fundamental language structure of % and _.
SELECT *\column-list FROM table_name
WHERE column LIKE 'XXXX%'
or
SELECT *\column-list FROM table_name
WHERE column LIKE '%XXXX%'
or
SELECT *\column-list FROM table_name
WHERE column LIKE 'XXXX_'
or
SELECT *\column-list FROM table_name
WHERE column LIKE '_XXXX'
or
SELECT *\column-list FROM table_name
WHERE column LIKE '_XXXX_'
You can consolidate N number of conditions utilizing AND OR administrators. XXXX could be any numeric or string esteem.
Example
Following are various models demonstrating WHERE part having diverse LIKE proviso with '%' and '_' administrators.
Sr.No | Statement & Description |
---|---|
1 |
WHERE SALARY LIKE '200%' Finds any values that start with 200 |
2 |
WHERE SALARY LIKE '%200%' Finds any values that have 200 in any position |
3 |
WHERE SALARY LIKE '_00%' Finds any values that have 00 in the second and third positions |
4 |
WHERE SALARY LIKE '2_%_%' Finds any values that start with 2 and are at least 3 characters in length |
5 |
WHERE SALARY LIKE '%2' Finds any values that end with 2 |
6 |
WHERE SALARY LIKE '_2%3' Finds any values that have a 2 in the second position and end with a 3 |
7 |
WHERE SALARY LIKE '2___3' Finds any values in a five-digit number that start with 2 and end with 3 |
Consider the CUSTOMERS table having the accompanying records.
ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 MP 4500.00
7 Muffy 24 Indore 10000.00
Following order is a model, which will show all the records from CUSTOMERS table where SALARY begins with 200.
SELECT * FROM CUSTOMERS
WHERE SALARY LIKE '200%';
The above order will deliver the accompanying yield.
ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
3 kaushik 23 Kota 2000.00