SQL Wildcards
SQL Wildcards
SQL wildcards are special characters
that are used in conjunction with the LIKE operator to perform pattern matching
in string values. They allow you to search for patterns in text data within
columns of a database table. The three most commonly used SQL wildcards are:
1.
% (percent): The
% (percent) wildcard matches zero or more characters. It can be used to
represent any sequence of characters.
Example: Suppose you have a
"customers" table with a "name" column, and you want to
find all customers whose name ends with "Smith". You can use the
following query:
SELECT * FROM customers WHERE name LIKE '%Smith';
This will return all rows from the
"customers" table where the "name" column ends with
"Smith".
2.
_ (underscore): The
_ (underscore) wildcard matches exactly one character. It can be used to
represent any single character.
Example: Suppose you have a
"products" table with a "product_code" column, and you want
to find all products with a product code that has exactly four characters,
where the second character is 'A'. You can use the following query:
SELECT * FROM products WHERE product_code LIKE '_A__';
This will return all rows from the
"products" table where the "product_code" column matches
the pattern 'A_', where the first character can be any character, the
second character is 'A', and the third and fourth characters can be any
characters.
3.
[] (square
brackets): The [] (square brackets) wildcard matches any single character that
is within the specified range or set of characters.
Example: Suppose you have a
"customers" table with a "country" column, and you want to
find all customers whose country is either "USA" or
"Canada". You can use the following query:
SELECT * FROM customers WHERE country IN ('USA', 'Canada');
This will return all rows from the
"customers" table where the "country" column matches either
"USA" or "Canada".
Examples:
Using the % Wildcard
The following SQL statement selects all customers with a City starting with "ber":
SELECT *
FROM Customers
WHERE City LIKE 'ber%';
The following SQL statement selects all customers with a City containing the pattern "es":
Example
SELECT *
FROM Customers
WHERE City LIKE '%es%';
Using the _ Wildcard
The following SQL statement selects all customers with a City starting with any character, followed by "ondon":
Example
SELECT *
FROM Customers
WHERE City LIKE '_ondon';
The following SQL statement selects all customers with a City starting with "L", followed by any character, followed by "n", followed by any character, followed by "on":
Example
SELECT *
FROM Customers
WHERE City LIKE 'L_n_on';
Using the [charlist] Wildcard
The following SQL statement selects all customers with a City starting with "b", "s", or "p":
Example
SELECT *
FROM Customers
WHERE City LIKE '[bsp]%';
The following SQL statement selects all customers with a City starting with "a", "b", or "c":
Example
SELECT *
FROM Customers
WHERE City LIKE '[a-c]%';
Using the [!charlist] Wildcard
The two following SQL statements select all customers with a City NOT starting with "b", "s", or "p":
Example
SELECT *
FROM Customers
WHERE City LIKE '[!bsp]%';
Or:
Example
SELECT *
FROM Customers
WHERE City NOT LIKE
'[bsp]%';
Note: The usage and behavior of wildcards may vary depending on the database system being used, as SQL implementations may have slight differences in wildcard syntax or behavior. It's recommended to refer to the specific documentation of the database system you are using for accurate and up-to-date information on SQL wildcards.
0 Comments