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.

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.

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.