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

select top 10 records in sql

trim sql example

sql diff between two dates

difference between distinct and unique in sql

sql check null

sql command to create table

union sql query

mysql select concat column values

sql dateadd format

sql server except example

You can also search these topics using sql wildcard characters, sql replace wildcard, sql wildcard search, wildcard sql server, sql server wildcard number, sql numeric wildcard, ms sql wildcard characters, escape character in sql, sql search string for character, sql partial string match, string matching sql, sql string match like.

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

oracle sql substring

sql join 2 tables

select max sql

sql month name from date

sql select database

sql server create table primary key

sql where and

sql replace multiple values in column

sql query month

update query in sql server

You can also search these topics using how to use like in sql, select like oracle, sql server like any, sql server like example, sql like expression, sql server like command, sql server like query example, sql string search, regexp_like sql server.