SQL WILDCARD Characters
A wildcard character can be used to compare a value to similar values or substitute for any other character(s) in a string.
To match a pattern from a word, special characters and wildcards characters may have used with SQL LIKE operator.
The SQL LIKE operator can be used with in any valid SELECT SQL statement.
Related Links
SQL Wildcard Characters
The following characters are used in as wildcard character with SQL LIKE operator.
Wildcard Character | Description |
---|---|
% | The percent sign character (%) substitute zero or more characters. |
_ | The underscore character ( _ ) substitute a single character. |
[charlist] Characters are seperated by comma(,) |
We can sets and ranges of characters and It represents only a single character from the charlist at once. |
[^charlist] or [!charlist] | Matches only a character NOT represents(other than the charlist) within the brackets. |
Sample Database Table - Books
BookId | BookName | BookPrice | RelYear | DomainName | AuthorName |
---|---|---|---|---|---|
1 | MySql All-in-One For Dummies | 80 | 2014 | Security | Siva Kumar |
2 | SQL in Easy Steps | 145 | 2015 | Performance | Hanumanthan |
3 | A Visual Introduction To Sql | 205 | 2008 | Optimization | Vinoth Kumar |
4 | Programming With Sql Server T-Sql | 200 | 2007 | Database | Dharan |
5 | Oracle 11g PL/SQL Programming | 70 | 2008 | Database | Vinoth Kumar |
6 | Foundations Of Sql Server 2008 | 110 | 2009 | Optimization | Padmavathi |
Using the SQL "%" Wildcard
The following SQL SELECT statement selects the all books with a BookName starting "M", in the "Books" table:
SELECT * FROM Books
WHERE
BookName LIKE 'M%';
The result of above query is:
BookId | BookName | BookPrice | RelYear | DomainName | AuthorName |
---|---|---|---|---|---|
1 | MySql All-in-One For Dummies | 80 | 2014 | Security | Siva Kumar |
The following SQL SELECT statement selects the all books with a BookName ending "ing", in the "Books" table:
SELECT * FROM Books
WHERE
BookName LIKE '%ing';
The result of above query is:
BookId | BookName | BookPrice | RelYear | DomainName | AuthorName |
---|---|---|---|---|---|
5 | Oracle 11g PL/SQL Programming | 70 | 2008 | Database | Vinoth Kumar |
The following SQL SELECT statement selects the all books with a BookName contains(anywhere in the bookname) "one", in the "Books" table:
SELECT * FROM Books
WHERE
BookName LIKE '%one%';
The result of above query is:
BookId | BookName | BookPrice | RelYear | DomainName | AuthorName |
---|---|---|---|---|---|
1 | MySql All-in-One For Dummies | 80 | 2014 | Security | Siva Kumar |
SQL LIKE Operator Example Using the SQL "_" Wildcard
The following SQL SELECT statement selects the all books with a BookPrice starting "1_0", in the "Books" table:
SELECT * FROM Books
WHERE
BookPrice LIKE '1_0';
The result of above query is:
BookId | BookName | BookPrice | RelYear | DomainName | AuthorName |
---|---|---|---|---|---|
6 | Foundations Of Sql Server 2008 | 110 | 2009 | Optimization | Padmavathi |
The following SQL SELECT statement selects the all books with a BookPrice starting (first any 2 characters then "5") "__5", in the "Books" table:
SELECT * FROM Books
WHERE
BookPrice LIKE '__5';
The result of above query is:
BookId | BookName | BookPrice | RelYear | DomainName | AuthorName |
---|---|---|---|---|---|
2 | SQL in Easy Steps | 145 | 2015 | Performance | Hanumanthan |
3 | A Visual Introduction To Sql | 205 | 2008 | Optimization | Vinoth Kumar |
Note: There are 2 underscore(_) in above statement.
SQL LIKE Operator Example Using the SQL "[charlist]" Wildcard
The following SQL SELECT statement selects the all books with a AuthorName starting "[PS]", in the "Books" table:
SELECT * FROM Books
WHERE
AuthorName LIKE '[PS]%';
The result of above query is:
BookId | BookName | BookPrice | RelYear | DomainName | AuthorName |
---|---|---|---|---|---|
1 | MySql All-in-One For Dummies | 80 | 2014 | Security | Siva Kumar |
6 | Foundations Of Sql Server 2008 | 110 | 2009 | Optimization | Padmavathi |
Note: The above results based on "MS Access" and "Sql Server" database only.
The following SQL SELECT statement selects the all books with a AuthorName starting "A", "B", "C", and "D", in the "Books" table:
SELECT * FROM Books
WHERE
AuthorName LIKE '[A-D]%';
The result of above query is:
BookId | BookName | BookPrice | RelYear | DomainName | AuthorName |
---|---|---|---|---|---|
4 | Programming With Sql Server T-Sql | 200 | 2007 | Database | Dharan |
Note: The above results based on "MS Access" and "Sql Server" database only.
The following SQL SELECT statement selects the all books with a Domain name not starting "D", "S", and "P", in the "Books" table:
SELECT * FROM Books
WHERE
DomainName LIKE '[!DSP]%';
The result of above query is:
BookId | BookName | BookPrice | RelYear | DomainName | AuthorName |
---|---|---|---|---|---|
3 | A Visual Introduction To Sql | 205 | 2008 | Optimization | Vinoth Kumar |
6 | Foundations Of Sql Server 2008 | 110 | 2009 | Optimization | Padmavathi |
Note: The above results based on "MS Access" and "Sql Server" database only.
Related Links