Which Operator Is Used To Search For A Specified Pattern In A Column?

SQL Wildcard Characters Example | SQL Wildcard Operators

Last updated December 21, 2019 0

SQL wildcard characters are used for substituting ane or more characters in a string. Wildcard operators or you lot can say characters are used with LIKE operators. The like operator is used with where clause for searching some specified design from a column. If you are unfamiliar with SQL datatype then check out my SQL Datatypes example on this blog.

SQL Wildcard Characters

The Types of SQL wildcard operators are the post-obit.
i) %
ii) _
3) [char list]

The pct sign represents zero, one or multiple characters. The underscore represents a single number or a character. These symbols can exist used in combinations.

Let's become in brief with all the characters 1 by 1.

% operator in SQL

This operator is used to notice any string from a cavalcade whose complete cord information is unknown to you. Let's empathize this with an example.

Table_1: Employee

Emp_id	Emp_name	City	State	Bacon
101	Rohit	Patna	Bihar	30000
201	Shivam	Jalandhar	Punjab	20000
301	Karan	Allahabad	Uttar Pradesh	40000
401	Suraj	Kolkata	Due west Bengal	60000
501	Akash	Vizag	Andhra Pradesh	70000

Example 1

Select * from employee where urban center like 'P%';

Output

SQL Wildcard Characters

Explanation

This query resulted in all the details of an employee whose city name starts with P.

Case ii

Select * from employee where city like '%a';

Output

SQL Wildcard Operators

Explanation

The above query resulted in all the details of an employee where the city proper noun ends with a.

Example 3

select * from employee where city like '%p%';

Output

SQL Wildcard Characters Example

Caption

The to a higher place query resulted in all the details of an employee whose city name has p in any position.

An _ operator in SQL

The _ operator is used as a substitute for 1 or more characters based on the number of times _ is used. 50et'southward understand this with an example. See the following tabular array.

Table_1: Employee

Emp_id	Emp_name	City	State	Salary
101	Rohit	Patna	Bihar	30000
201	Shivam	Jalandhar	Punjab	20000
301	Karan	Allahabad	Uttar Pradesh	40000
401	Suraj	Kolkata	West Bengal	60000
501	Akash	Vizag	Andhra Pradesh	70000

Case i

Select * from employee where city similar '_atna';

Output

SQL Wildcard Operators Tutorial

Explanation

The above SQL statements select all employees whose city starts with any character followed by 'atna'.

Example 2

select * from employee where urban center like 'Kol_a_a';

Output:

SQL Wildcard

Explanation:

The post-obit SQL statement selects all types of employees whose city starts with Kol followed by any character than a followed by a grapheme then at last ends with a.

Operations performed by both % and _ operator

Table_1: Employee

Emp_id	Emp_name	City	State	Bacon
101	Rohit	Patna	Bihar	30000
201	Shivam	Jalandhar	Punjab	20000
301	Karan	Allahabad	Uttar Pradesh	40000
401	Suraj	Kolkata	West Bengal	60000
501	Akash	Vizag	Andhra Pradesh	70000

Example 1

Select * from employee where state like '_u%';

Output

Operations performed by both % and _ operator

Explanation

The higher up query volition print the details of an employee whose state proper noun 1 st position get-go with any character followed past u and rest whatsoever other characters.

Instance 2

select * from employee where country like 'a_%_%';

Output

% and _ operator

Caption

The higher up query volition print the details of an employee whose state name one st position start with A, followed by whatsoever other characters merely should be of at least three characters in length.

[char list] operator

The char list operator is used for representing whatever unmarried character within the brackets.

Suppose, if we declare h[ao]t in a query and so the resulting set will consist of either lid every bit output or hot as output simply not striking as i is not present in the brackets.

Let's clear this with more example.

Consider the following tabular array.

Table_1: Employee

Emp_id	Emp_name	City	State	Salary
101	Rohit	Patna	Bihar	30000
201	Shivam	Jalandhar	Punjab	20000
301	Karan	Allahabad	Uttar Pradesh	40000
401	Suraj	Kolkata	Due west Bengal	60000
501	Akash	Vizag	Andhra Pradesh	70000

Instance i

select * from employee where city similar '[PJA]%';

Output

Emp_id	Emp_name	City	Country	Bacon
101	Rohit	Patna	Bihar	30000
201	Shivam	Jalandhar	Punjab	20000
301	Karan	Allahabad	Uttar Pradesh	40000

Explanation

The above query will print the details of the employee whose urban center the first character either starts with 'P' or 'J' or 'A'.

Example 2

select * from employee where city like '[!PJA]%';

Select * from employee where urban center like '[^PJA]%';

Output

Emp_id	Emp_name	City	State	Bacon
401	Suraj	Kolkata	West Bengal	60000
501	Akash	Vizag	Andhra Pradesh	70000

Explanation

The above query will print the details of the employee whose city the starting time character does not starting time with 'P' or 'J' or 'A'. Hither this query was used for fetching non-matching set or range of characters specified inside the brackets.

Example three

Select * from employee where city like '[P-Z]%';

Output

Emp_id	Emp_name	City	Country	Salary
101	Rohit	Patna	Bihar	30000
501	Akash	Vizag	Andhra Pradesh	70000

Explanation

In this query details of all employees are displayed whose urban center the kickoff character lies in the range from P to Z . Northwardote: P and Z are inclusive.

#SQL Wildcard Characters

A wildcard grapheme is used to substitute one or more characters in a cord.

Wildcard characters are used with the SQL LIKE operator. The LIKE operator is used in a WHERE clause to search for a specified blueprint in a column.

#Wildcard Characters in MS Access

Symbol	Description	Case
*	Represents zero or more characters	bl* finds bl, black, bluish, and hulk.
?	Represents a single character	h?t finds a hot, lid, and hit.
[]	Represents any single character within the brackets	h[oa]t finds hot and hat, but not striking.
!	Represents any character, not in the brackets	h[!oa]t finds striking, but not hot and hat.
–	Represents a range of characters	c[a-b]t finds cat and cbt.
#	Represents any single numeric graphic symbol	two#v finds 205, 215, 225, 235, 245, 255, 265, 275, 285, and 295.

#Wildcard Characters in SQL Server

Symbol	Description	Example
%	Represents aught or more characters.	bl% finds bl, black, blue, and blob.
_	Represents a single character.	h_t finds a hot, hat, and hit.
[]	Represents any unmarried grapheme within the brackets.	h[oa]t finds hot and chapeau, simply not hit.
^	Represents any character, not in the brackets.	h[^oa]t finds hitting, but non hot and hat.
–	It represents a range of characters.	c[a-b]t finds cat and cbt.

All the wildcards tin likewise be used in combinations!

Here are some examples showing different LIKE operators with '%' and '_' wildcards:

Similar Operator	Description
WHERE CustomerName Like 'a%'	Finds any values that get-go with "a".
WHERE CustomerName Similar '%a'	Finds any values that finish with "a".
WHERE CustomerName LIKE '%or%'	Finds any values that have "or" in whatever position.
WHERE CustomerName Similar '_r%'	Finds any values that have "r" in the 2d position.
WHERE CustomerName Similar 'a_%_%'	Finds whatsoever values that start with "a" and are at to the lowest degree 3 characters in length.
WHERE ContactName LIKE 'a%o'	Finds any values that commencement with "a" and ends with "o".

Finally, SQL Wildcard Characters Example | SQL Wildcard Operators Tutorial is over.