SQL SELECT statement with Where LIKE

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