WILDCARD IN SQL
Like clause is used along with SQL SELECT statement to find string matches pattern in given Column value.
Using % Wildcard.
id | Emp_name | Age | Salary |
1 | Jebas | 35 | 40,000 |
2 | Thomas | 30 | 80,000 |
3 | Mark | 25 | 40,000 |
4 | Harry | 42 | 50,000 |
5 | Willam | 45 | 100,000 |
SELECT [Emp_name]
,[Age]
,[salary]
FROM Employee WHERE Emp_name like 't%';
This statement like 't%' , return emp_name STARTING with character 't'.
Emp_name | Age | Salary |
Thomas | 30 | 80,000 |
SELECT [Emp_name]
,[Age]
,[salary]
FROM [followlect].[dbo].[Employee] WHERE Emp_name like '%k'
This statement like '%k' , return emp_name ENDING with character 'k'.
Emp_name | Age | Salary |
Mark | 25 | 40,000 |
SELECT [Emp_name]
,[Age]
,[salary]
FROM [followlect].[dbo].[Employee] WHERE Emp_name like '%a%'
This statement like '%a%' , return emp_name having character 'a'.in any position.
id | Emp_name | Age | Salary |
1 | Jebas | 35 | 40,000 |
2 | Thomas | 30 | 80,000 |
3 | Mark | 25 | 40,000 |
4 | Harry | 42 | 50,000 |
5 | Willam | 45 | 100,000 |
Using _ Wildcard.
id | Emp_name | Age | Salary |
1 | Jebas | 35 | 40,000 |
2 | Thomas | 30 | 80,000 |
3 | Mark | 25 | 40,000 |
4 | Harry | 42 | 50,000 |
5 | Willam | 45 | 100,000 |
6 | James | 32 | 35000 |
SELECT [Emp_name]
,[Age]
,[salary]
FROM Employee WHERE Emp_name like 'j___s';
id | Emp_name | Age | Salary |
1 | Jebas | 35 | 40,000 |
6 | James | 32 | 35000 |
SELECT [Emp_name]
,[Age]
,[salary]
FROM Employee WHERE Emp_name like '_h%';
This statement return Emp_name that have 'h' as second character.
Emp_name | Age | Salary |
Thomas | 30 | 80,000 |
Using [ ] wilcard
id | Emp_name | Age | Salary |
1 | Jebas | 35 | 40,000 |
2 | Thomas | 30 | 80,000 |
3 | Mark | 25 | 40,000 |
4 | Harry | 42 | 50,000 |
5 | Willam | 45 | 100,000 |
6 | James | 32 | 35000 |
7 | Mock | 22 | 15000 |
SELECT [Emp_name]
,[Age]
,[salary]
FROM Employee WHERE Emp_name like 'm[a-o][c-r]k';
[a-o] specify the range, ie character between a to o
Emp_name | Age | Salary |
Mark | 25 | 40,000 |
Mock | 22 | 15000 |
SELECT [Emp_name]
,[Age]
,[salary]
FROM Employee WHERE Emp_name like '[a-m]%';
This statement retrun Emp_name starting character between range a to m.
Emp_name | Age | Salary |
Jebas | 35 | 40,000 |
Mark | 25 | 40,000 |
Harry | 42 | 50,000 |
James | 32 | 35000 |
Mock | 22 | 15000 |
SELECT [Emp_name]
,[Age]
,[salary]
FROM Employee WHERE Emp_name like '[atm]%';
This statement return Emp_name starting character a or t or m.
Emp_name | Age | Salary |
Thomas | 30 | 80,000 |
Mark | 25 | 40,000 |
Mock | 22 | 15000 |
Using ^ Wildcard.
^ specify not in.
SELECT [Emp_name]
,[Age]
,[salary]
FROM Employee WHERE Emp_name like '[^atm]%';
This statement return Emp_name starting character not in a or t or m.
Emp_name | Age | Salary |
Jebas | 35 | 40,000 |
Harry | 42 | 50,000 |
Willam | 45 | 100,000 |
James | 32 | 35000 |
Thank You. Like Share and Subscribe 👍 folowlect.com
Comments
Post a Comment