INDEX: SQL Tutorial

SQL | Wildcard operator

A wildcard character is used to replace  any character(s) in a string. Wildcard operator are used with SQL Like operator . The link to SQL WHERE Clause in mentioned in the article. Like operator are used with SQL WHERE CLAUSE to search for a particular pattern mentioned in the table/column.

SQL Wildcard operators—> [SQL Like opearator+ SQL WHERE CLAUSE]

Wildcard operators deals with 4 different types of operators:

Operators Description
 %  This operator represents zero or more characters
 _  This operator represents a single character in a string
 [range of characters]   It defines set of characters and gives a range of characters    to match
 [!range_character] or[^range_character]   Defines sets and ranges of character not to match

Note1 : MS Access uses a “?”(question mark) symbol instead of  “_”(underscore) symbol.
Note2: Combination of Wildcard operators are feasible. “%”, “_” can be used together.

Wildcard operators are very often used in large databases. Combination of LIKE operator and Wildcard proves to be very effective .

Examples showing different LIKE operators with ‘%’ and ‘_’ wildcards:

LIKE Operator Description
 WHERE name/example LIKE ‘h%’  Helps in finding the value that start with ‘h’
  WHERE name/example LIKE ‘%h’  Find values that end with ‘h’
  WHERE name/example LIKE  ‘%singh%’  Find values that have singh in it
 WHERE name/example LIKE    ‘_i%’  Helps in finding the values that have “i” in 2 pos.
 WHERE name/example LIKE    ‘s_%_%’  Find any values with atleast ‘3’ char as len. and   start   with ‘s’
WHERE name/example LIKE     ‘s%h’  Values starting with ‘s’ and ends with ‘h’ are to be   picked

Therfore, we saw the examples of combination of WHERE clause and LIKE operator using Wildcard operator. [Wildcard operators- “%”,”_”]


Different Views of Wildcard operators

Let us take a deep dive ,and explore Wildcard operators using different examples.

Demo Database: Padhle

Table Name:Employee

EmployeeID Name City Pincode
1   Harsh  Mumbai  303007
 2   Harshit  Srinagar  690390
3  Sunny  Alberta  696909
4  Young thug  michigan  1010109
5  Anuj  Varanasi   2090789

 


Using the % Wildcard

Syntax:

SELECT */column_name FROM 'Table_name'  WHERE 'Column_name' LIKE 'Wildcard operator'

Example1:

SELECT City FROM Employee WHERE City LIKE 'Va%';

The following SQL statement selects City with name starting from “Va”:

City
    Varanasi

Example 2:

SELECT Name from Employee WHERE Name LIKE '%ars%';

The following SQL statement selects Employee with Name containing the pattern-‘ars’.

Name
  Harsh
  Harshit

Using the _ Wildcard

Syntax:

SELECT */column_name FROM 'Table_name'  WHERE 'Column_name' LIKE 'Wildcard operator'

This operator represents a single character in a string.

Example 1:

SELECT * FROM Employee WHERE pincode LIKE '_007';

The following statement selects all rows from Employee with a pincode starting with any digit,followed by’007′.

EmployeeID Name City Pincode
3   Sunny  Alberta  303007

Example 2:

SELECT * FROM Employee WHERE pincode LIKE '69_9_9';

The following statement will search for the pincode with ’69’ as starting number ,followed by any character,followed by ‘9 ‘,followed by any character,then followed by ‘9’.

EmployeeID Name City Pincode
1   Harsh  Mumbai  696909

Using the [range of characters/charlist]

Syntax:

SELECT */column_name FROM 'Table_name'  WHERE 'Column_name' LIKE 'Wildcard operator'

Example 1:

SELECT * FROM Employee WHERE City LIKE '[MSA]%';

The following SQL statement selects all with a City starting with “M”, “S”, or “A”:

EmployeeID Name City Pincode
1   Harsh  Mumbai  303007
 2   Harshit  Srinagar  690390
3  Sunny  Alberta  696909

Using the [!range_character/charlist]

Syntax:

SELECT */column_name FROM 'Table_name'  WHERE 'Column_name' LIKE 'Wildcard operator'

Example1:

SELECT  * FROM Employee WHERE Name LIKE '[!SAH]%';

The following SQL statement selects all the pincode where ‘Name’ is not starting from ‘S’,’A’,’H’.

EmployeeID Name City Pincode
4   Young thug  michigan  1010109

Example 2:

 

SELECT * FROM Customers WHERE City NOT LIKE '[SAH]%';

Note: NOT LIKE also serves the same purpose and output will be same for both the cases.

Leave a Comment

Your email address will not be published. Required fields are marked *