WEB DEVELOPER SITE
HTMLCSSJAVASCRIPTSQLPHPBOOTSTRAPJQUERYANGULARXML
 

SQL Wildcards


Wildcards can be used in place of any other characters in a string.


SQL wildcard

In SQL, wildcards are used with the SQL LIKE operator.

SQL wildcards are used to search data in a table.

The following wildcard characters can be used in SQL:

wildcard Description
% Replace 0 or more characters
_ Substitute a character
[ charlist ] Any single character in the character column
[^ charlist ]
or
[! charlist ]
Any single character not in the character column


Demo database

In this tutorial, we will use the welookups sample database.

Here is the data from the "Websites" table:

+----+--------------+---------------------------+-------+---------+
| id | name         | url                       | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1  | Google       | https://www.google.cm/  | 1     | USA     |
| 2  | Indiatimes          | https://www.Indiatimes.com/ | 13    | IN            |
| 3  | zomato      | http://www.zomato.com/  | 4689  | IN            |
| 4  | indiamart          | http://indiamart.com/       | 20    | IN            |
| 5  | Facebook     | https://www.facebook.com/| 3     | USA     |
| 7  | stackoverflow | http://stackoverflow.com/|   0 | IND     |
+----+---------------+---------------------------+-------+---------+


Use SQL% wildcard

The following SQL statement selects all sites whose urls start with the letter "https":

Example

SELECT * FROM Websites
WHERE url LIKE 'https%';

Execution output:

The following SQL statement selects all sites whose url contains the pattern "oo":

Example

SELECT * FROM Websites
WHERE url LIKE '% oo%';

Execution output:



Use SQL _ wildcards

The following SQL statement selects all customers whose name starts with an arbitrary character and then "oogle":

Examples

SELECT * FROM Websites
WHERE name LIKE '_oogle';

Execution output:

The following SQL statement selects all sites whose name starts with "G", then an arbitrary character, then "o", then an arbitrary character, and then "le":

Example

SELECT * FROM Websites
WHERE name LIKE 'G_o_le';

Execution output:



Use SQL [charlist] wildcard

The REGEXP or NOT REGEXP operator (or RLIKE and NOT RLIKE) is used in MySQL to manipulate regular expressions.

The following SQL statement selects all sites whose name starts with "G", "F", or "s":

Example

SELECT * FROM Websites
WHERE name REGEXP '^ [GFs]';

Execution output:

The following SQL statement selects sites whose names begin with letters A through H:

Example

SELECT * FROM Websites
WHERE name REGEXP '^[A-H]';

Execution output:

The following SQL statement selects websites whose names do not start with the letters A through H:

Example

SELECT * FROM Websites
WHERE name REGEXP '^ [^ A-H]';


Welookups is optimized for learning.Copy right 2018 kumar aditya singh .
All Right Reserved and you agree to have read and accepted our term and condition.
All Rights Reserved.

welookups.com