SQL Wildcard Operators

SQL Wildcard Operators are used to substitute any other character(s) in a string.

  • SQL database support two Wildcard characters operators in conjunction with the LIKE operator.
  • The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

SQL WILDCARD Types

There are Two type of Wildcards used in SQL databse:

PERCENT '%' SQL Wildcard

  • '%' Percent Wildcard is used to matches one or more characters.
  • BUT MS Access uses the asterisk (*) wildcard character instead of the percent sign (%) wildcard character.

UNDERSCORE '_' SQL Wildcard

  • '_' Underscore Wildcard is used to matche one character.
  • BUT MS Access uses a question mark (?) instead of the underscore (_) to match any one character.

LIKE Operator with discription

Patterns with Operator Description
Where column_name LIKE 'b%' Finds any values that START with "b" in that column
Where column_name LIKE '%b' Finds any values that END with "b" in that column
Where column_name LIKE '%b%' Finds any values that have "b" in any position
Where column_name LIKE '_a%' Finds any values that have "a" in any seccond position
Where column_name LIKE 'b__%' Finds any values that START with "b" and at least 3 characters in lenght
Where column_name LIKE 'b%c' Finds any values that START with "b" and END with "c"

EXAMPLE: SQL Wildcards

Student Table

A table of student records, the name of table is "Student", and the name of columns are [ROLL_NO, STUDENT_NAME, ADDRESS, MOBILE_NO, AGE, and BRANCH]:

ROLL_NO STUDENT_NAME ADDRESS MOBILE_NO AGE BRANCH
1 Mohit Sharma Delhi XXXXXXXX87 20 IT
2 Pooja Pandit Kolkata XXXXXXXX34 18 CSE
3 Priya Pandey Delhi XXXXXXXX62 19 EEE
4 Mohit Kumar Jha Allahabad U.P XXXXXXXX87 20 ECE
5 Shevani Shree Noida U.P XXXXXXXX51 17 IT
6 Badshah Mumbai XXXXXXXX02 20 Civil
7 Viki Mohan Pune XXXXXXXX72 19 CSE
8 Sona Kaur Indor M.P XXXXXXXX81 22 ME

EXAMPLE '%' Wildcard with [LIKE Operator] 'b%'

SELECT  *
FROM Student
WHERE STUDENT_NAME LIKE 'p%';

Result Look Like

ROLL_NO STUDENT_NAME ADDRESS MOBILE_NO AGE BRANCH
2 Pooja Pandit Kolkata XXXXXXXX34 18 CSE
3 Priya Pandey Delhi XXXXXXXX62 19 EEE

EXAMPLE '%' Wildcard with [LIKE Operator] '%a'

SELECT  *
FROM Student
WHERE STUDENT_NAME LIKE '%a';

Result Look Like

ROLL_NO STUDENT_NAME ADDRESS MOBILE_NO AGE BRANCH
1 Mohit Sharma Delhi XXXXXXXX87 20 IT
4 Mohit Kumar Jha Allahabad U.P XXXXXXXX87 20 ECE

EXAMPLE '_' and '%' Wildcard with [LIKE Operator] '_o%'

SELECT  *
FROM Student
WHERE STUDENT_NAME LIKE '_o%';

Result Look Like

ROLL_NO STUDENT_NAME ADDRESS MOBILE_NO AGE BRANCH
1 Mohit Sharma Delhi XXXXXXXX87 20 IT
2 Pooja Pandit Kolkata XXXXXXXX34 18 CSE
4 Mohit Kumar Jha Allahabad U.P XXXXXXXX87 20 ECE
8 Sona Kaur Indor M.P XXXXXXXX81 22 ME

Policy

GDATAMART.COM is optimized for learning & job information; The users try to improve your knowledge and practical experience through this site but we cannot warrant full correctness of all content... Learn More → | Sitemap →

© 2020 GDATAMART | All Rights Reserved