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.


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.

Search Keys

  • sql wildcard characters
  • sql replace wildcard
  • wildcard matching
  • sql wildcard search
  • wildcard sql server
  • sql server wildcard number
  • sql numeric wildcard
  • wildcard example
  • wildcard operator
  • wildcard expression
  • ms sql wildcard characters
  • escape character in sql
  • sql search string for character
  • sql partial string match
  • sql string match
  • sql match
  • string matching sql
  • sql string match like
  • like sql
  • sql like in

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 Troubleshooting SQL Server 84.22 2015 Security Ranjani Mai
6 Advanced SQL Programming 120 2007 Security Balan
7 Oracle 11g PL/SQL Programming 70 2008 Database Vinoth Kumar
8 SQL All-in-One For Dummies 160 2014 Programming Geetha
9 Oracle Query Performance Tuning 71.87 2012 Performance Hanumanthan
10 Foundations Of Sql Server 2008 110 2009 Optimization Padmavathi
11 Making Sense Of SQL 75 2008 Database Padmavathi
12 Art Of SQL 178.69 2013 Programming Keshavan

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
11 Making Sense Of SQL 75 2008 Database Padmavathi

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
6 Advanced SQL Programming 120 2007 Security Balan
7 Oracle 11g PL/SQL Programming 70 2008 Database Vinoth Kumar
9 Oracle Query Performance Tuning 71.87 2012 Performance Hanumanthan

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
8 SQL All-in-One For Dummies 160 2014 Programming Geetha

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 Advanced SQL Programming 120 2007 Security Balan
8 SQL All-in-One For Dummies 160 2014 Programming Geetha
10 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
10 Foundations Of Sql Server 2008 110 2009 Optimization Padmavathi
11 Making Sense Of SQL 75 2008 Database 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
6 Advanced SQL Programming 120 2007 Security Balan

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
10 Foundations Of Sql Server 2008 110 2009 Optimization Padmavathi

Note: The above results based on "MS Access" and "Sql Server" database only.


Search Keys

  • sql like example
  • how to use like in sql
  • select like oracle
  • sql like _
  • sql like percent
  • sql server like any
  • sql server like example
  • sql like expression
  • like sql example
  • sql server like command
  • sql server like query example
  • sql string search
  • regex like sql
  • t sql regexp
  • regexp_like sql server
  • sql like regex
  • regex sql
  • regexp_like examples
  • oracle regex
  • regex function