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

Share this website !!!
Facebook Twitter Linkedin

1. Online Compilers - Compile & Run your C/C++ programs:

✅ Online Compilers

2. Ask your questions or clarify your/others doubts from,

✅ Online Discussion Forums by Fresh2Refresh.com





Close
We Support JALLIKATTU !!!
We Support JALLIKATTU!
By Fresh2Refresh.com
Show Your Support as well. Thanks!