x

SQL – LIKE & Wildcard operators

Prev     Next

SQL LIKE & Wildcard operators – LIKE operator in SQL is used with a WHERE clause to search specific patterns in a table column. To make searching effective, there are 2 wild card operators available in SQL which are used along with the LIKE operator. They are,


  1. % (percentage) operator
  2. _ (underscore) operator

Wildcard operators are used as a substitute for one or more than one characters while searching.

  • % (Percentage) – Using this wildcard operator, we can match from 0 upto many characters in a string or column’s value.
  • _ (Underscore) – Using this wildcard operator, we can only match one character in a string or column’s value.
  • [char_list] – Using this syntax, we can specify more than one character in ‘char_list’ to search and match in a given string.
  • [^char_list] or [!char_list] – Using this syntax, we can specify more than one character in ‘char_list’ to search and not to match in a given string i.e. ignoring these characters. Syntax for SQL LIKE & Wildcard operators is given below.

Syntax for SQL LIKE & Wildcard operators:

SQL-wildcard operators SQL Syntax with
LIKE operator
% (Percentage operator) SELECT column_name1, column_name2, etc
FROM table_name
WHERE  column_name1 LIKE ‘XX%’;
SELECT column_name1, column_name2, etc
FROM table_name
WHERE  column_name1 LIKE ‘%XX’;
SELECT column_name1, column_name2, etc
FROM table_name
WHERE  column_name1 LIKE ‘%XX%’;
_ (Underscore operator) SELECT column_name1, column_name2, etc
FROM table_name
WHERE  column_name1 LIKE ‘XX_’;
SELECT column_name1, column_name2, etc
FROM table_name
WHERE  column_name1 LIKE ‘_XX’;
SELECT column_name1, column_name2, etc
FROM table_name
WHERE  column_name1 LIKE ‘_XX_’;
[char_list] SELECT column_name1, column_name2, etc
FROM table_name
WHERE  column_name1 LIKE ‘[xyz]%’;
SELECT column_name1, column_name2, etc
FROM table_name
WHERE  column_name1 LIKE ‘[x-z]%’;Note:  [x-z] means, starting from x and ending in z. I.e. It is equal to x, y, z. This is also applicable for numerical values.
[^char_list] or
[!char_list]		 SELECT column_name1, column_name2, etc
FROM table_name
WHERE  column_name1 LIKE ‘[!xyz]%’;
SELECT column_name1, column_name2, etc
FROM table_name
WHERE  column_name1 LIKE ‘[!x-z]%’;

Note: [char_list] or [!char_list] syntax can be used with % and _ wildcards in any combination and order.


Example for how to use SQL LIKE & Wildcard operators in a WHERE clause in SELECT queries:

Please consider the following table with few records as given below.

Table name (for example): student
Column names in this table: Student_ID, Student_name, City and Age
Available records: 4 rows

sql-table1

Please refer below examples, SQL queries and output to know how LIKE and wildcard operators work with different syntax and combinations.

SQL queries/Output
Explanation
SQL Query:
SELECT Student_ID
FROM table_name
WHERE City LIKE ‘De%’;SQL Query Output:
Student_ID
2		 This SQL will search City; starts with ‘De’ and remaining characters may be anything.
SQL Query:
SELECT Student_ID
FROM table_name
WHERE  City LIKE ‘%ne’;SQL Query Output:
Student_ID
4		 This SQL will search City; ends with ‘ne’ and remaining characters may be anything.
SQL Query:
SELECT Student_ID
FROM table_name
WHERE City LIKE ‘%n%’;SQL Query Output:
Student_ID
1
4		 This SQL will search City; with ‘n’ and remaining starting, ending characters may be anything. An ending character could be ‘n’ also.
SQL Query:
SELECT Age FROM table_name
WHERE  Student_name LIKE ‘Man_’;SQL Query Output:
Age
26		 This SQL will search Student_name; starts with ‘Man’ and next only one character may be anything.
SQL Query:
SELECT Age FROM table_name
WHERE  Student_name LIKE ‘_aju’;SQL Query Output:
Age
25		 This SQL will search Student_name; ends with ‘aju’ and the only first character may be anything.
SQL Query:
SELECT Age FROM table_name
WHERE  Student_name
LIKE ‘Thi_a_a_aja_’;SQL Query Output:
Age
27		 As explained above, each _ (underscore) can be any single character in the exact position and remaining characters should be as given in this query.
SQL Query:
SELECT Age FROM table_name
WHERE  Student_name
LIKE ‘[RT]%’;SQL Query Output:
Age
25
27		 This SQL will search Student_name; start with either R or T and ends with any character.
SQL Query:
SELECT Age FROM table_name
WHERE  Student_name
LIKE ‘[!RT]%’;SQL Query Output:
Age
26
28		 This SQL will search Student_name; start with neither R nor T and ends with any character.

Note: If no records matching with any of the above criteria, then no records will be selected and displayed in the output.

Prev     Next



Like it? Please Spread the word!