Need help?

Our experts have had an average response time of 13.52 minutes in October 2021 to fix urgent issues.

We will keep your servers stable, secure, and fast at all times for one fixed price.

How To Use Wildcards in SQL Server?

by | Feb 13, 2021

Wondering how to use Wildcards in SQL Server? We can help you.

Wildcards are special placeholder characters that can represent one or more other characters or values.

Like many computer languages, SQL allows the use of various wildcard characters.

Wildcards allow us to search the database for any data without knowing the exact values held within it.

Here at Bobcares, we often use Wildcards for our customers using SQL as a part of our Server Management Services.

Today let’s see the steps our Support Engineers follow for using Wildcards.

How to query data using Wildcards in SQL Server

Now we will see how our Support Techs use SQL’s designated wildcards.

We will consider Ubuntu 20.04 server, a running rational database and MySQL installed and secure on the server as prerequisites.

The steps to follow for using Wildcards in SQL Server is given below:

1. Connecting to MySQL and Setting up a Sample Database

First, we will log in to the server from our local machine via SSH if our SQL database system runs on a remote server:

$ ssh [email protected]_server_ip

Then open the MySQL server prompt, replacing bob with the name of your MySQL user account.

Using the following command:

$ mysql -u bob -p

Next, we will create a database named wildcardsDB using the following command:

mysql> CREATE DATABASE wildcardsDB;

If the database was created successfully, we will receive output like this:

Query OK, 1 row affected (0.01 sec)

To select the wildcardsDB database, we can run the following USE statement:

mysql> USE wildcardsDB;

Database changed

After selecting the database, you can create a table within it with the following command. As an example, let us say you wanted to create a table named user_profiles to hold the profile information of users of an application.
This table will hold the following five columns:

1. user_id: This column will hold values of the int data type. It will also serve as the table’s primary key, with each value functioning as a unique identifier for its respective row.
2. name: Each user’s name, expressed using the varchar data type with a maximum of 30 characters.
3. email: This column will hold users’ email addresses, also expressed using the varchar data type but with a maximum of 40 characters
4. birthdate: Using the date data type, this column will hold each user’s date of birth.
5. quote: Each user’s favorite quote. To provide an adequate number of characters for quotes, this column also uses the varchar data type, but with a maximum of 300 characters.

We will run the following command to create this sample table:

mysql> CREATE TABLE user_profiles (
mysql> user_id int,
mysql> name varchar(30),
mysql> email varchar(40),
mysql> birthdate date,
mysql> quote varchar(300),
mysql> PRIMARY KEY (user_id)
mysql> );

Database changed

Then we ill insert some sample data into the empty table:

mysql> INSERT INTO user_profiles
mysql> VALUES
mysql> (1, 'Kim', '[email protected]', '1945-07-20', '"Never let the fear of striking out keep you from playing the game." -Babe Ruth'),
mysql> (2, 'Ann', '[email protected]', '1947-04-27', '"The future belongs to those who believe in the beauty of their dreams." -Eleanor Roosevelt'),
mysql> (3, 'Phoebe', '[email protected]', '1950-07-17', '"100% of the people who give 110% do not understand math." -Demitri Martin'),
mysql> (4, 'Jim', '[email protected]', '1940-08-13', '"Whoever is happy will make others happy too." -Anne Frank'),
mysql> (5, 'Timi', '[email protected]', '1940-08-04', '"It is better to fail in originality than to succeed in imitation." -Herman Melville'),
mysql> (6, 'Taeko', '[email protected]', '1953-11-28', '"You miss 100% of the shots you don\'t take." -Wayne Gretzky'),
mysql> (7, 'Irma', '[email protected]', '1941-02-18', '"You have brains in your head. You have feet in your shoes. You can steer yourself any direction you choose." -Dr. Seuss'),
mysql> (8, 'Iris', '[email protected]', '1961-01-05', '"You will face many defeats in life, but never let yourself be defeated." -Maya Angelou');
Query OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates: 0 Warnings: 0

2. Querying Data with Wildcards

Wildcards are special placeholder characters that can represent one or more other characters or values.

In SQL, there are only two defined wildcard characters:

a) _: When used as a wildcard, an underscore represents a single character. For example, b_b would match bob, bib, or bab.
b) %: The percentage sign wildcard represents zero or more characters. For example, bo%c would match bobc, bobbbc, or boc.

These wildcards are used exclusively in a query’s WHERE clause with either the LIKE or NOT LIKE operators.

Let us consider that we know at least one of the users listed in the user_profiles table has a name that’s three letters long and ends with “im” but we are not sure who they are. We can run the following query, which uses the _ wildcard, to find who they are:

mysql> SELECT * FROM user_profiles WHERE name LIKE '_im';

+---------+------+---------------------+------------+---------------------------------------------------------------------------------+
| user_id | name | email | birthdate | quote |
+---------+------+---------------------+------------+---------------------------------------------------------------------------------+
| 1 | Kim | [email protected] | 1945-07-20 | "Never let the fear of striking out keep you from playing the game." -Babe Ruth |
| 4 | Jim | [email protected] | 1940-08-13 | "Whoever is happy will make others happy too." -Anne Frank |
+---------+------+---------------------+------------+---------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

The NOT LIKE operator has the opposite effect of LIKE. Rather than returning every record that matches the wildcard pattern, it will return every row that does not match the pattern.

To see this difference we will run the previous query again after replacing LIKE with NOT LIKE:

mysql> SELECT * FROM user_profiles WHERE name NOT LIKE '_im';

This time, every row where the value in the name column matches _im is omitted from the result set:

+---------+--------+----------------------------+------------+--------------------------------------------------------------------------------------------------------------------------+
| user_id | name | email | birthdate | quote |
+---------+--------+----------------------------+------------+--------------------------------------------------------------------------------------------------------------------------+
| 2 | Ann | [email protected] | 1947-04-27 | "The future belongs to those who believe in the beauty of their dreams." -Eleanor Roosevelt |
| 3 | Phoebe | [email protected] | 1950-07-17 | "100% of the people who give 110% do not understand math." -Demitri Martin |
| 5 | Timi | [email protected] | 1940-08-04 | "It is better to fail in originality than to succeed in imitation." -Herman Melville |
| 6 | Taeko | [email protected] | 1953-11-28 | "You miss 100% of the shots you don't take." -Wayne Gretzky |
| 7 | Irma | [email protected] | 1941-02-18 | "You have brains in your head. You have feet in your shoes. You can steer yourself any direction you choose." -Dr. Seuss |
| 8 | Iris | [email protected] | 1961-01-05 | "You will face many defeats in life, but never let yourself be defeated." -Maya Angelou |
+---------+--------+----------------------------+------------+--------------------------------------------------------------------------------------------------------------------------+
6 rows in set (0.00 sec)

We must keep in mind that wildcards are different from regular expressions. Generally, a wildcard refers to a character used in glob-style pattern matching, while regular expressions rely on a regular language to match string patterns.

Escaping Wildcard Characters

There may be times when you want to search for data entries that contain one of SQL’s wildcard characters.

Here we can use an escape character that will instruct SQL to ignore the wildcard function of either % or _ and instead interpret them as plain text.

Let’s consider that we know a couple of the users listed in the database have a favorite quote that includes a percentage sign, but we don’t know exactly who they are.

We can try running the following query:

mysql> SELECT user_id, name, quote FROM user_profiles WHERE quote LIKE '%';

However, this query will not be very helpful. As the percentage sign serves as a stand-in for any string of characters of any length, it will return every row in the table:

+---------+--------+--------------------------------------------------------------------------------------------------------------------------+
| user_id | name | quote |
+---------+--------+--------------------------------------------------------------------------------------------------------------------------+
| 1 | Kim | "Never let the fear of striking out keep you from playing the game." -Babe Ruth |
| 2 | Ann | "The future belongs to those who believe in the beauty of their dreams." -Eleanor Roosevelt |
| 3 | Phoebe | "100% of the people who give 110% do not understand math." -Demitri Martin |
| 4 | Jim | "Whoever is happy will make others happy too." -Anne Frank |
| 5 | Timi | "It is better to fail in originality than to succeed in imitation." -Herman Melville |
| 6 | Taeko | "You miss 100% of the shots you don't take." -Wayne Gretzky |
| 7 | Irma | "You have brains in your head. You have feet in your shoes. You can steer yourself any direction you choose." -Dr. Seuss |
| 8 | Iris | "You will face many defeats in life, but never let yourself be defeated." -Maya Angelou |
+---------+--------+--------------------------------------------------------------------------------------------------------------------------+
8 rows in set (0.00 sec)

To correct this, we need to include percentage sign wildcards at the beginning and end of the search pattern following the LIKE operator:

mysql> SELECT user_id, name, quote FROM user_profiles WHERE quote LIKE '%\%%';

+---------+--------+----------------------------------------------------------------------------+
| user_id | name | quote |
+---------+--------+----------------------------------------------------------------------------+
| 3 | Phoebe | "100% of the people who give 110% do not understand math." -Demitri Martin |
| 6 | Taeko | "You miss 100% of the shots you don't take." -Wayne Gretzky |
+---------+--------+----------------------------------------------------------------------------+
2 rows in set (0.00 sec)

In this query, the backslash only escapes the second percentage sign, while the first and third ones are still acting as wildcards. Thus, this query will return every row whose quote column includes at least one percentage sign.

We can also define custom escape characters with the ESCAPE clause, as given in the following example:

mysql> SELECT user_id, name, email FROM user_profiles WHERE email LIKE '%@_%' ESCAPE '@';

+---------+--------+----------------------------+
| user_id | name | email |
+---------+--------+----------------------------+
| 1 | Kim | [email protected] |
| 3 | Phoebe | [email protected] |
| 4 | Jim | [email protected] |
| 5 | Timi | [email protected] |
| 7 | Irma | [email protected] |
+---------+--------+----------------------------+
5 rows in set (0.00 sec)

This query defines the @ sign as an escape character and returns every row whose email column contains at least one underscore.

Conclusion

To conclude we saw how to use and escape wildcards SQL-based databases. Also, we saw the steps that our Support techs follow fr using Wildcards in SQL server for our customers.

 

  

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

GET STARTED

var google_conversion_label = "owonCMyG5nEQ0aD71QM";

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

Privacy Preference Center

Necessary

Necessary cookies help make a website usable by enabling basic functions like page navigation and access to secure areas of the website. The website cannot function properly without these cookies.

PHPSESSID - Preserves user session state across page requests.

gdpr[consent_types] - Used to store user consents.

gdpr[allowed_cookies] - Used to store user allowed cookies.

PHPSESSID, gdpr[consent_types], gdpr[allowed_cookies]
PHPSESSID
WHMCSpKDlPzh2chML

Statistics

Statistic cookies help website owners to understand how visitors interact with websites by collecting and reporting information anonymously.

_ga - Preserves user session state across page requests.

_gat - Used by Google Analytics to throttle request rate

_gid - Registers a unique ID that is used to generate statistical data on how you use the website.

smartlookCookie - Used to collect user device and location information of the site visitors to improve the websites User Experience.

_ga, _gat, _gid
_ga, _gat, _gid
smartlookCookie

Marketing

Marketing cookies are used to track visitors across websites. The intention is to display ads that are relevant and engaging for the individual user and thereby more valuable for publishers and third party advertisers.

IDE - Used by Google DoubleClick to register and report the website user's actions after viewing or clicking one of the advertiser's ads with the purpose of measuring the efficacy of an ad and to present targeted ads to the user.

test_cookie - Used to check if the user's browser supports cookies.

1P_JAR - Google cookie. These cookies are used to collect website statistics and track conversion rates.

NID - Registers a unique ID that identifies a returning user's device. The ID is used for serving ads that are most relevant to the user.

DV - Google ad personalisation

IDE, test_cookie, 1P_JAR, NID, DV, NID
IDE, test_cookie
1P_JAR, NID, DV
NID
hblid

Security

These are essential site cookies, used by the google reCAPTCHA. These cookies use an unique identifier to verify if a visitor is human or a bot.

SID, APISID, HSID, NID, PREF
SID, APISID, HSID, NID, PREF