SQL Wildcard Operators - The Coding Shala

Home >> Learn SQL >> SQL Wildcard Operator

SQL Wildcard Operators

The Wildcard operators are used with the SQL LIKE Clause. SQL LIKE is used in the WHERE clause to search for a specified pattern in the columnThere are two wildcards used in conjunction with the LIKE operator.
  1.  The percent sign(%) - represents zero, one or multiple characters.
  2.  The underscore(_) - represents a single character.
These symbols can be used together.
SQL Wildcard Operators - The Coding Shala
  • The following table shows some examples of the like operator with % and _ wildcards - 
 LIKE Operator             Description  
 where Name LIKE 'a%'      Finds any values that start with "a"  
 where Name LIKE '%a'      Finds any values that end with "a"  
 where Name LIKE '%a%'     Finds any values that have "a" in any position  
 where Name LIKE '_a%'     Finds any values that have "a" in the second position  
 where Name LIKE 'a_%_%'   Finds any values that start with "a" and are at least 3 characters in length  
 where Name LIKE 'a%e'     Finds any values that start with "a" and ends with "e" 

SQL Wildcard Example 

The following 'emp' table will be used for the examples - 
 Id     Name     Age     Address     Salary  
 1     Akshay     22     Pune        40000  
 2     Mohit      21     Delhi       42000  
 3     Akash      21     Delhi       45000  
 4     Nikhil     24     Mumbai      50000  
 5     Smith      24     Pune        50000  
 6     Akshay     22     Pune        50000  
 7     Nikhil     24     Mumbai      43000  

The following query will return all the rows that have Name starts with 'A' - 

SQL >> select * from emp  
        where Name like 'A%';  
 
Output >>  
 Id     Name     Age     Address     Salary  
 1     Akshay     22     Pune       40000  
 3     Akash      21     Delhi      45000  
 6     Akshay     22     Pune       50000  


The following SQL query return all the records that have the second character of the Name is 'o' - 

SQL >> select * from emp  
        where Name like '_o%';;  
 
Output >>  
 Id     Name     Age     Address     Salary  
 2     Mohit     21      Delhi       42000 


Other Posts You May Like
Prev<< SQL NOT Operator                                                                        NEXT >>SQL IN Operator
Please leave a comment below if you like this post or found some error, it will help me to improve my content.

Comments

Popular Posts from this Blog

Shell Script to find sum, product and average of given numbers - The Coding Shala

Single Number 3 LeetCode Solution - The Coding Shala

LeetCode - Number of Good Pairs Solution - The Coding Shala

Shell Script to Create a Simple Calculator - The Coding Shala

Java Method Overloading - The Coding Shala