DB2

SQL wildcards use in Queries

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.

SQL Wildcards Syntax

SELECT column_name
FROM table_name 
WHERE column_xxxx LIKE wildcard_operator;

Wildcard Characters in MS Access

SymbolDescriptionExample
*Represents zero or more charactersbl* finds bl, black, blue, and blob
?Represents a single characterh?t finds a hot, hat, and hit
[]Represents any single character within the bracketsh[oa]t finds hot and hat, but not hit
!Represents any character, not in the bracketsh[!oa]t finds hit, but not hot and hat
Represents any single character within the specified rangec[a-b]t finds cat and cbt
#Represents any single numeric character2#5 finds 205, 215, 225, 235, 245, 255, 265, 275, 285, and 295

Wildcard Characters in SQL Server

Sr.NoRepresentation SymbolDescription
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.
5Stands for a range of characters.

Here are some examples showing different LIKE operators with ‘%’ and ‘_’ wildcards:

Sr.NoUsage with LIKE operatorDescription
1WHERE columnName LIKE ‘a%’Returns values that start with ‘a’.
2WHERE columnName LIKE ‘%a’Returns values that end with ‘a’.
3WHERE columnName LIKE ‘%ab%’Returns any value that has ‘ab’ in any position.
4WHERE columnName LIKE ‘_a%’Returns any value which has ‘a’ as the second character.
5WHERE columnName LIKE ‘a_%_%’Returns any value that starts with ‘a’ and is at least 3 characters in length.
6WHERE columnName LIKE ‘a%x’Returns any value which starts with ‘a’ and ends with ‘x’.

Examples of SQL Wildcards

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

Using the % SQL Wildcards

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 

Using the _ SQL Wildcards

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 

Using the [charlist] SQL Wildcards

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 

Using the [!charlist] SQL Wildcards

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 

Read DB2 blogs: Click Here IBM DB2 Manual:Click Here

Admin

Share
Published by
Admin

Recent Posts

Increase Transparency and Collaboration Product Backlog

A well-maintained product backlog is crucial for successful product development. It serves as a single…

3 weeks ago

Product Backlog – Incremental value to the customer

Incremental value to the customer refers to the gradual delivery of small, functional parts of…

4 weeks ago

Product Market, Customer’s Desire, Need, and Challenges

A Product Market refers to the group of potential customers who might be interested in…

4 weeks ago

PAL-EBM Professional Agile Leadership – EBM Certification

The Professional Agile Leadership - Evidence-Based Management (PAL-EBM) certification offered by Scrum.org is designed for…

4 months ago

PAL I Professional Agile Leadership Certification

The Professional Agile Leadership (PAL I) certification, offered by Scrum.org, is designed to equip leaders…

4 months ago

Scrum Master Certification: CSM, PSM, SSM

Choosing the right Scrum Master Certification depends on your current experience and career goals. If…

6 months ago