
How to Use Wild Cards in SQL Server
In this tutorial you will learn how to use wildcards in SQL Server. SQL wildcards can be used when searching for data in a database. SQL wildcards are used with the LIKE operator, which acts as a comparison when searching the database. We will use the ‘%’ and ‘_’ wildcards in SQL Server:
% - A substitute for zero or more characters.
_ - A substitute for exactly one character.
We will show an example of both using our Movies table.
The % Wildcard
The % wildcard searches the database for matches of zero or more characters. We will look for titles that start with ‘so’.
1 2 |
SELECT * FROM Movies WHERE Title LIKE 'so%' |
Your output should be Source Code and Soul Surfer. The ‘%’ symbol after ‘so’ means that anything can follow as long as the title starts with ‘so’.
We can also use a variation that looks for titles that contains specified letters. We will look for directors that contain the pattern ‘ur’ in their name.
1 2 |
SELECT * FROM Movies WHERE Director LIKE '%ur%' |
Your output should be Limitless and The Lincoln Lawyer. Taking a page out of the previous example, we now used the ‘%’ symbol to find anything that precedes and proceeds the pattern ‘ur’.
The _ Wildcard
Like the previous wildcard, there are two forms of the _ wildcard. The first form looks for a word that begins with any character followed by a specified pattern as such:
1 2 |
SELECT * FROM Movies WHERE Title LIKE '_op' |
The output will be Hop. In this example, we looked for a title than begins with anything as long as the last letters are ‘op’.
The second form plays off of the first one, just more in detail. We will search for titles that begin with ‘R’, followed by any character, followed by ‘n’, followed by any character, followed by ‘o’.
1 2 |
SELECT * FROM Movies WHERE Title LIKE 'R_n_o' |
The output is Rango. This form is for helps finding commonly misspelled words easier.
Wildcards serve as an easier way of searching wide variety of words in a database, especially in the bigger databases.
Thanks for reading and make sure to download the source files to get a better understanding of how the code works.
2 Responses
12.26.2011
This is one of the best answer so far, I have read online. Just useful information. Very well presented. I had found another nice post with wonderful explanation on wildcards in sql server over internet.
please check out this link…
http://mindstick.com/Articles/8009b2bd-a5ec-427a-ae50-003a850a7e0e/?SQL%20Wildcards
Thanks
10.3.2012
how can i create wide table in sql server 2008 r2. please provide the syntax for creating wide table..