SQL SERVER – Tips from the SQL Development Series – Wildcard – Querying Special Characters – Day 2 of 35

In this blog post we will learn various tips related to Querying Special Characters with the help of wildcard in SQL Server.

Querying Special Characters

Some special characters can be tricky to pattern match since they themselves can represent different values at different times. Let’s look at some examples. Here is a quick look at all the records in the [Grant] table of the JProCo database. Note: Since [Grant] is also a keyword it must be enclosed in square brackets or double quotes to designate it as the [Grant] table and now the keyword. Take a look at many of the names in the Grant Name field and notice we have many names with special symbols in them. See figure below:

Finding literal % signs with wildcards

We learned about special characters in yesterdays post called wildcards. When using the percentage sign % or the underscore _ we can do relative searches. We have a grant called “92 Purr_Scents %% team” which has a percentage symbol in the name. We have other grants with percentages in their names. How do you search for a percentage sign with two wildcards on either side? It would appear to SQL that you’re looking for three wildcards as seen in the query below:

--Bad query pattern logic (finds all Grant records)
SELECT *
FROM [GRANT]
WHERE GrantName LIKE '%%%'

We have three special characters and no literal percent symbol. Help is on the way again with the square brackets. Take the wild card you want to use as a literal percentage symbol and surround it with square brackets. You see two grants having a percentage symbol within their names. In this example the square brackets give you the literal percentage symbol. In this figure you see just the two grants that have a % sign in the name.

Finding literal _ signs with wildcards

You may know that the underscore is also a wildcard. We can use this to find a specific second letter. How many Grants have the letter A for the second letter can be found with the following query:

--Find Grants where A is the 2nd letter.
SELECT *
FROM [GRANT]
WHERE GrantName LIKE '_A%'

In this  example by asking for one character before the letter A and any amount afterward, we names like “TALTA_Kishan International” and “Call Mom @Com”. The % symbol wildcard can represent many characters while the _ symbol wildcard always represents exactly one.

To find the Grants with underscores in the name we do the same technique we used with the % wildcard. Again, we take the wildcard that you want to evaluate and put it in square brackets.

You see three grants having underscores in their names (Figure 2.20). In this example the square brackets tell SQL you are looking for a literal underscore character.

Note: If you want to setup the sample JProCo database on your system you can watch this video.

Question 2:

Q 2.) You want to find all grant names that have an Underscore as the second letter. Which SQL code would you use?

1. SELECT * FROM [Grant] WHERE GrantName like ‘_[_]% ‘
2. SELECT * FROM [Grant] WHERE GrantName like ‘[_]_% ‘
3. SELECT * FROM [Grant] WHERE GrantName like ‘_%[_]%_ ‘
4. SELECT * FROM [Grant] WHERE GrantName = ‘_[_]% ‘
5. SELECT * FROM [Grant] WHERE GrantName = ‘[_]_% ‘
6. SELECT * FROM [Grant] WHERE GrantName = ‘_%[_]%_ ‘

Please post your answer in the comment section to win Joes 2 Pros books.

Rules:

Please leave your answer in the comment section below with correct option, explanation and your country of resident.
Every day one winner will be announced from the United States.
Every day one winner will be announced from India.
A valid answer must contain country of residence of answerer.
Please check my Facebook page for winners name and correct answer.
Winner will get Joes 2 Pros Volume 1.
The contest is open till next blog post shows up at which is next day GTM+2.5.

Reference:  Pinal Dave (https://blog.sqlauthority.com)