To write a good performing procedure or function we should be aware about available wildcards in SQL Server. I found most developer depends on ‘%’ wildcard only while we have others as well. In this article we will discuss all the available wildcards in detail with examples. First of all let’s see when we need wildcards, sometimes we need to search records on the basis of some pattern then we need these wildcards, here is available wildcards in SQL Server
% To match zero or more character
_ To Match one character
[] To match any character in [character list]
[^] To match any character not in [character list] opposite of []
Let’s see how to use these useful wildcards, we will use all the examples with this very basic table Customers
CustomerID FirstName LastName Address
1001 Jacky John 127 Park Avenue
1002 Jack Smith A-923, Tank Apartments
1003 Abhay Jeet Lane 2, Colonel Street
1004 Grand Benson A-92 C, Park Street
To search those customers, whose address contain “Park”
SELECT * FROM Customers WHERE Address like ‘%Park%”
-- RESULT
CustomerID FirstName LastName Address
1001 Jacky John 127 Park Avenue
1004 Grand Benson A-92 C, Park Street
To search those customers, whose address ends with “Street”
SELECT * FROM Customers WHERE Address like ‘%Street”
-- RESULT
CustomerID FirstName LastName Address
1003 Abhay Jeet Lane 2, Colonel Street
1004 Grand Benson A-92 C, Park Street
To search those customers, whose address starts with “A-92”
SELECT * FROM Customers WHERE Address like ‘A-92%”
-- RESULT
CustomerID FirstName LastName Address
1002 Jack Smith A-923, Tank Apartments
1004 Grand Benson A-92 C, Park Street
Now let’s check for single character matches
Suppose we have to search customers whose first name start with “J” then any single character then “ck” followed by any character then we can use
SELECT * FROM Customers WHERE FirstName like ‘J_ck%”
-- RESULT
CustomerID FirstName LastName Address
1001 Jacky John 127 Park Avenue
1002 Jack Smith A-923, Tank Apartments
[character list] To get those customers whose address having at least one digit
SELECT * FROM Customers WHERE address Like ‘%[0123456789]%’
Or
SELECT * FROM Customers WHERE address Like ‘%[0-9]%’
To get those customers whose ID in range 1002 to 1009
SELECT * FROM Customers WHERE CustomerID Llike ‘100[2-9]’
To get those whose ID in range 1050 to 1099
SELECT * FROM Customers WHERE CustomerID Llike ‘100[5-9][0-9]'
[^character list] In the same way we can use [^] by placing ^ inside square bracket, for example suppose we don't want those customers whose Id ends with 7, 8 or 9 then, we can use
SELECT * FROM Customers WHERE CustomerID Like ‘%[^7-9]%’
|
|
| By Dev D On 25 Feb, 13 Viewed: 678 |
We can not directly change a column to identity columns, say we have a primary key column or any normal column in our table and want to make it identity column then we cannot do it directly by altering the table but there are only two way to change column to identity column 1. Create a new... By Ali Adravi On 02 May 2013 Viewed: 1,096
There are many ways which can help us to save our time to write some repeated code or to do some repeated action, in this post we will see some very common code which we need very frequently in our development life. I am not saying that after this post you will save your half say work every day but... By Ali Adravi On 31 Mar 2013 Viewed: 936
In SQL server there are decimal and money data type to store precision and scale both together or say decimal values. Suppose we want to get precision and scale separately then how to get it. There are many ways to do this but there is a function in SQL named PARSENAME which can be used to get... By Myghty On 26 Mar 2013 Viewed: 3,394
When we need to concatenate two columns simply we can use `+` sign and that is correct, but what if any of them is null, Will it return what we want, NO, it will return null. So let's discuss how we should concatenate two or more columns without creating extra space or extra comma problem. Let's... By Ali Adravi On 13 Feb 2013 Viewed: 12,778
TRY…CATCH constructs do not trap the following conditions: - Warnings or informational messages that have a severity of 10 or lower. - Errors that have a severity of 20 or higher those stop the SQL Server Database Engine task processing for the session. If an error occurs that has severity of... By Ali Adravi On 28 Dec 2012 Viewed: 586