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 column. There 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.
By

Comments

Post a Comment

Popular Posts from this Blog

Java Program to Convert Binary to Decimal - The Coding Shala

Home >> Java Programs >> Convert Binary to Decimal  Hey there, welcome back to another post. In this post, we will learn how to convert Binary to Decimal in Java. Java Program to Convert Binary to Decimal You have given a binary number, convert it to a decimal number. Example:  Input: 101 Output: 5 Input: 111 Output: 7 Binary to Decimal Conversion in Java Approach We will the below formula to convert binary to decimal: decimal number = (2^0) * (rightmost digit) + (2^1) * (second rightmost digit) + ...  for example:  binary number = 110 decimal number = 2^0 * 0 + 2^1 * 1 + 2^2 * 1 = 1 * 0 + 2 * 1 + 4 * 1 = 0 + 2 + 4 = 6 Java Program:  import java.util.Scanner; /** * https://www.thecodingshala.com/ */ public class Main { public static void printDecimal( int num) { int decimal = 0; int twos = 1; // 2^0 = 1 initial value while (num > 0) { int temp = num
Read more

N-th Tribonacci Number Solution - The Coding Shala

Home >> Programming >> N-th Tribonacci Number  Hey there, welcome back to another post. In this post, we will learn how to solve the N-th Tribonacci Number problem and will implement its solution in Java. N-th Tribonacci Number Problem Statement The Tribonacci sequence Tn is defined as follows: T0 = 0, T1 = 1, T2 = 1, and Tn+3 = Tn + Tn+1 + Tn+2 for n >= 0. Given n, return the value of Tn.  Example 1:  Input: n = 4 Output: 4 Explanation: T_3 = 0 + 1 + 1 = 2 T_4 = 1 + 1 + 2 = 4 N-th Tribonacci Number Java Solution using Bottom-Up DP This problem is similar to the Fibonacci series. We can solve it using the Bottom-Up approach of dynamic programming. Time Complexity: O(n) Space Complexity: O(n) The space complexity can be reduced to O(1) by using variables to store the previous three values instead of using an array. Java Program:  class Solution { public int tribonacci( int n) { if (n == 0) return 0; if (n == 1 || n == 2
Read more

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

Home >> Scripting >> Sum, product and average Shell Script to find the sum, product, and the average of given numbers Read four integer numbers from the user and find the sum, product, and average of these four numbers? Code:  #! / bin / bash echo "enter four integers" read a b c d sum =$(echo "$a + $b + $c + $d" | bc - l) average=$(echo "$sum / 4" | bc - l) product=$(echo "$a * $b * $c * $d" | bc - l) echo "sum = $sum" echo "Average = $average" echo "Product = $product" Other Posts You May Like Shell script to print sum of the digits of a number Shell Script to print number in descending order Shell script to find out biggest number from three numbers Shell script to find out factorial of a number Shell Script to create a simple calculator Please leave a comment below if you like this post or found some error, it will help me to improve my content.
Read more

Shell Script to Create a Simple Calculator - The Coding Shala

Home >> Scripting >> simple calculator Shell Script to Create a Simple Calculator Write a shell script to create a simple calculator using a switch-case statement? Code:  #! / bin / bash echo "simple calculator" sum = 0 i = "y" echo "enter first number" read n1 echo "enter second number" read n2 while [ $ i = "y" ] do echo "1.Addition" echo "2.Subtraction" echo "3.Multiplication" echo "4.Division" echo "Enter choice" read ch case $ch in 1 ) sum =$(echo " $n1 + $n2" | bc - l) echo "Addition is =" $ sum ;; 2 ) sum =$(echo "$n1 - $n2" | bc - l) echo "Sub is =" $ sum ;; 3 ) sum =$(echo "$n1 * $n2" | bc - l) echo "Mul is =" $ sum ;; 4 ) sum =$(echo "$n1 / $n2" | bc - l) echo "div is =" $ sum ;; * )echo "invalid choice" esac echo "Do y
Read more

Java Program to Convert Decimal to Binary - The Coding Shala

Home >> Java Programs >> Convert Decimal to Binary  Hey there, welcome back to another post. In this post, we will learn how to convert decimal to binary in Java. Java Program to Convert Decimal to Binary You have given a decimal number. Write a Java program to convert decimal to binary. Example 1:  Input: 7 Output: 111 Input: 10 Output: 1010 Decimal to Binary Conversion using Array Approach To convert decimal to binary we can follow the below steps: Store the remainder in the array when we divide the number by 2. Divide the number by 2. Follow the above two steps until the number is above 0. Print the array in reverse order. For example:  4 % 2 = 0 => 4/2 = 2 2 % 2 = 0 => 2/2 = 1 1 % 2 = 1 => 1/2 = 0 Binary of 4 is: 100 Java Program:  import java.util.ArrayList; import java.util.List; import java.util.Scanner; /** * https://www.thecodingshala.com/ */ public class Main { public static void printBinary( int num) {
Read more