SQL Like Wildcard :
In my previous articles i have given SQL tutorials with real life examples. In this article i would like to give you the SQL Like Wildcard information with different examples.SQL Like Wildcard operator is most used and very important operator used in real life examples.The SQL Like Wildcard uses for pattern checking in the table and specific pattern matching.I have already explained SQL like Wildcard in my previous article of operators in SQL. I would like to write a separate article on this topic because it is most used operator in SQL.
SQL Like Wildcard examples :
In this section i would like to explain different examples of SQL Like operator.The SQL Like operator uses in where condition which will gives us information or data about the specific pattern from the table.SQL like operator always used in Where condition of SQL statement. If user tries to use this operator anywhere else then the SQL engine will fire the syntax error.Before checking examples of SQL Like wildcard operator first let us check the syntax of the operator.As it is not a function you need to use that operator in where condition of SQL.
Select like ‘%Amit%’ ,Name from Employee; –Wrong
The above query will fire the Syntax error.
Select Name from Employee where name like ‘%Amit%’; –Right
The above query will give the name of Employees from Employee table where name like ‘Amit’.
Syntax :
Select column_name1,column_name2….column_name_n from tablename where column_name like ‘[Wildcard character] String [Wildcard character];
The above syntax explains about the SQL Like operator and how it is used in SQL statements.As I told you it is always used in where clause of SQL statement.In like operator string you will see the Wildcard character which is most important part of SQL Like statement.
Wildcard Characters used in Like operator:
There are two types of wildcard characters used in SQL Like operator.
- Percent operator (%)
- Underscore operator (_)
% Wildcard Character :
This is very important wildcard character which uses to check one or more characters in pattern matching. % Wildcard character is same as * operator in Microsoft access which checks the string pattern matching.
_ Wildcard Character:
This is also important wildcard character which uses to check or process only one character at a time._ wildcard character is same as ? character which used to indicate a single character at a time in Microsoft Access.
I would like to give you different syntax of Like operator with examples in following section. These examples are really very useful examples in real life. These kind of examples are used in day to day real industry examples.
Example 1 : Searching whole string
User can search whole string from the table using % Wildcard.
Syntax :
Select column_name1,column_name2….column_name_n from tablename where column_name like ‘%String-to-be-searched %’;
The above syntax is used to search the whole string from the table.
If user wants to search employees whose name is ‘Amit’ then following query is helpful.
Example :
Select * from Employees where Employee_name like ‘%Amit%’;
Example 2 : Searching part of the string which starts with some string
The second example of % wildcard character is searching the part of the string which starts with some string or letter.Only one % wildcard is helpful in that case.
Syntax :
Select column_name1,column_name2….column_name_n from tablename where column_name like ‘String-to-be-searched %’;
If user wants to search employees whose name starts with ‘Amit’ then following query is helpful.
Example :
Select * from Employees where Employee_name like ‘Amit%’;
Example 3 : Searching part of the string which ends with some string
Using the % wildcard character user can search the string which ends with some letter or which ends with some string.User needs to use percentage sign before the string or character which needs to be searched.
Syntax :
Select column_name1,column_name2….column_name_n from tablename where column_name like ‘%String-to-be-searched ‘;
If user wants to search the employees whose name ends with ‘mit’ following query is useful.
Example :
Select * from Employees where Employee_name like ‘%mit’;
Example 4 : Search the string from the table which has specific pattern like second letter is A
Using the Percent wildcard as well as _ wildcard user can achieve this.
Examples :
Select * from Employees where Employee_name like ‘_a%’;
The above statement will give all the employees where Employee name have letter a in second position.
Select * from Employees where City like ‘P_ _e’;
The above statement will give the data of all employees where city starts with P and ends with e which has two characters in between.
Example 5: Search a pattern which has length of 4 characters which starts with A and Ends with T
You can search the specific pattern of the string from table which has specific conditions. This can be achieved by using the _ wildcard character of like operator.
Example:
Select * from Employees where Employee_name like ‘A_ _ T’;
The above statement fetches all the information about employees from Employees table where name starts with A and Ends with T which has 4 characters.
Example 6 : Search the names of Employees starts with letter A,S,D
User can search the set of data in which name starts with some letter or name not starts with some letters or characters by using character set wildcard. [Character_set] is used to show the data which starts with set of characters.
Example:
Select * from Employees where Employee_name like ‘[ASD]%’;
The above example will give the set of Employees whose name starts with A or S or D.
If user wants to search the name of Employees whose name starts with A,B,C then following query is used.
Example:
Select * from Employees where Employee_name like ‘[A-C]%’;
Same as character set operator if you start that operator with ! (not) operator it will change it to not like.
If user wants to search the name of the Employees whose name not start with A,S,D then following query is useful:
Example:
Select * from Employees where Employee_name like ‘[!ASD]%’;
These are above some important examples of SQL Like wildcard.Hope you like this article on SQL Like Wildcard.If you like this article or if you have any concerns or suggestions with the same kindly comment it in comments section.