SQL wildcards are used to substitute one or more characters in a string. We use SQL wildcards when we need to search for complex data. This complex data could compromise strings or numerical with special characteristics. Wildcards also come in handy when we want to speed up our querying process. The results are considerably fast when wildcards are in use. Wildcards come in handy when we need to compare the strings and also aim to get the minute details. Wildcards are put up with the LIKE operator and come in handy for solving complex queries.
SELECT column_name FROM table_name WHERE column_xxxx LIKE wildcard_operator;
Symbol | Description | Example |
* | Represents zero or more characters | bl* finds bl, black, blue, and blob |
? | Represents a single character | h?t finds a hot, hat, and hit |
[] | Represents any single character within the brackets | h[oa]t finds hot and hat, but not hit |
! | Represents any character, not in the brackets | h[!oa]t finds hit, but not hot and hat |
– | Represents any single character within the specified range | c[a-b]t finds cat and cbt |
# | Represents any single numeric character | 2#5 finds 205, 215, 225, 235, 245, 255, 265, 275, 285, and 295 |
Sr.No | Representation Symbol | Description |
1 | % | Stands for zero or more characters. |
2 | _ | Stands for a single character. |
3 | [ ] | Stands for a single character specific to the brackets. |
4 | ^ | Stands for any character not in brackets. |
5 | – | Stands for a range of characters. |
Here are some examples showing different LIKE operators with ‘%’ and ‘_’ wildcards:
Sr.No | Usage with LIKE operator | Description |
1 | WHERE columnName LIKE ‘a%’ | Returns values that start with ‘a’. |
2 | WHERE columnName LIKE ‘%a’ | Returns values that end with ‘a’. |
3 | WHERE columnName LIKE ‘%ab%’ | Returns any value that has ‘ab’ in any position. |
4 | WHERE columnName LIKE ‘_a%’ | Returns any value which has ‘a’ as the second character. |
5 | WHERE columnName LIKE ‘a_%_%’ | Returns any value that starts with ‘a’ and is at least 3 characters in length. |
6 | WHERE columnName LIKE ‘a%x’ | Returns any value which starts with ‘a’ and ends with ‘x’. |
Table - Customers | ID | EMPLOYEE | DEPT | CITY | SALARY | SALES | +——+----------+-----+-----------+----------+----- | 1 | Satvi | HR | Chapls | 9000.00 | 90000 | 2 | Kannav | MKT| BBSR | 5000.00 | 50000 | 3 | Kiran | MKT| Balasore| 8000.00 | 50000 | 4 | Lilly | IT | Mhills | 6000.00 | 40000 | 5 | Alex | IT | Whills | 8500.00 | 80000 | 6 | Stacy | PR | Malibu | 4500.00 | 20000 | 7 | Barbie | PR | Ventura | 1000.00 | 25000
Example1: The following SQL statement selects all customers with a City starting with “B%”:
SELECT * FROM Customers WHERE City LIKE ‘B%'; RESULT: | ID | EMPLOYEE | DEPT | CITY | SALARY | SALES | +——+----------+-----+-----------+----------+----- | 2 | Kannav | MKT| BBSR | 5000.00 | 50000 | 3 | Kiran | MKT| Balasore| 8000.00 | 50000
Example2: The following SQL statement selects all customers with a City containing the pattern “il”:
SELECT * FROM Customers WHERE City LIKE '%il%'; RESULT: | ID | EMPLOYEE | DEPT | CITY | SALARY | SALES | +——+----------+-----+-----------+----------+----- | 4 | Lilly | IT | Mhills | 6000.00 | 40000 | 5 | Alex | IT | Whills | 8500.00 | 80000
Example1: The following SQL statement selects all customers with a City starting with any character, followed by “hills”:
SELECT * FROM Customers WHERE City LIKE '_hills'; RESULT: | ID | EMPLOYEE | DEPT | CITY | SALARY | SALES | +——+----------+-----+-----------+----------+----- | 4 | Lilly | IT | Mhills | 6000.00 | 40000 | 5 | Alex | IT | Whills | 8500.00 | 80000
Example2: The following SQL statement selects all customers with a City has 2nd character with “h”, followed by any two characters, followed by “ls”:
SELECT * FROM Customers WHERE City LIKE ‘_h_ _ls’; RESULT: | ID | EMPLOYEE | DEPT | CITY | SALARY | SALES | +——+----------+-----+-----------+----------+----- | 1 | Satvi | HR | Chapls | 9000.00 | 90000 | 4 | Lilly | IT | Mhills | 6000.00 | 40000 | 5 | Alex | IT | Whills | 8500.00 | 80000
Example1: The following SQL statement selects all customers with a City starting with “B”, or “M”:
SELECT * FROM Customers WHERE City LIKE '[BM]%'; RESULT: | ID | EMPLOYEE | DEPT | CITY | SALARY | SALES | +——+----------+-----+-----------+----------+----- | 2 | Kannav | MKT| BBSR | 5000.00 | 50000 | 3 | Kiran | MKT| Balasore| 8000.00 | 50000 | 4 | Lilly | IT | Mhills | 6000.00 | 40000 | 6 | Stacy | PR | Malibu | 4500.00 | 20000
Example2: The following SQL statement selects all customers with a City starting with “A”, “B”, or “C”:
SELECT * FROM Customers WHERE City LIKE ‘[A-C]%'; RESULT: | ID | EMPLOYEE | DEPT | CITY | SALARY | SALES | +——+----------+-----+-----------+----------+----- | 1 | Satvi | HR | Chapls | 9000.00 | 90000 | 2 | Kannav | MKT| BBSR | 5000.00 | 50000 | 3 | Kiran | MKT| Balasore| 8000.00 | 50000
The two following SQL statements select all customers with a City NOT starting with “B”, or “M”:
SELECT * FROM Customers WHERE City LIKE ‘[!BM]%'; or SELECT * FROM Customers WHERE City NOT LIKE ‘[BM]%'; RESULT: | ID | EMPLOYEE | DEPT | CITY | SALARY | SALES | +——+----------+-----+-----------+----------+----- | 1 | Satvi | HR | Chapls | 9000.00 | 90000 | 4 | Lilly | IT | Mhills | 6000.00 | 40000 | 6 | Stacy | PR | Malibu | 4500.00 | 20000
A well-maintained product backlog is crucial for successful product development. It serves as a single…
Incremental value to the customer refers to the gradual delivery of small, functional parts of…
A Product Market refers to the group of potential customers who might be interested in…
The Professional Agile Leadership - Evidence-Based Management (PAL-EBM) certification offered by Scrum.org is designed for…
The Professional Agile Leadership (PAL I) certification, offered by Scrum.org, is designed to equip leaders…
Choosing the right Scrum Master Certification depends on your current experience and career goals. If…