SQL Like Statement (or) Condition is basically used for checking if a particular charter string appears in a given string or not. We can use wildcard symbols in the where clause and also in the CASE Statements for pattern matching. Because of this, the SQL Like operator is more powerful than using the equal to (=) or not equal to (! =) symbols for pattern matching and more generically for string comparison. The basic point that we need to keep in mind while using the SQL Like operator is; the result set returned by it is a Boolean value, meaning a TRUE or FALSE.
SQL Like Syntax
The basic syntax for SQL Like is shown below.
match_expression [ NOT ] LIKE pattern [ ESCAPE escape_character ]
SQL Like – Wildcards
The following are the wildcards that can be used with the SQL like condition – %( Percentage symbol), _ (Underscore), [] (Square Parenthesis), ^ (Caret symbol); will show each of them with examples…
Firstly, let us create a temporary table and insert some data into it and see how we can use the SQL Like operator.
CREATE TABLE #SQL_Like (NameID int, ContactName nvarchar(50))
INSERT INTO #SQL_Like Values (1,'Amy')
INSERT INTO #SQL_Like Values (2,'Anna')
INSERT INTO #SQL_Like Values (3,'Brenda')
INSERT INTO #SQL_Like Values (4,'Catherine')
INSERT INTO #SQL_Like Values (5,'Daniel')
INSERT INTO #SQL_Like Values (6,'Emilio')
INSERT INTO #SQL_Like Values (7,'Edna')
INSERT INTO #SQL_Like Values (8,'Frances')
INSERT INTO #SQL_Like Values (9,'Gustavo')
INSERT INTO #SQL_Like Values (10,'Hannah')
INSERT INTO #SQL_Like Values (11,'Ian')
INSERT INTO #SQL_Like Values (12,'John')
Examples for using SQL Like Condition
The most basic form to start with, suppose from the above table if we want to get all the names starting with “A” we can use the below query.
SELECT * FROM #SQL_Like WHERE ContactName Like 'A%'
The “%” percentage wild card is used for doing a pattern match of any string of zero or more characters. And this is the result that we would get once we are done with executing the above line of T-SQL.
NameID ContactName
1 Amy
2 Anna
If we want to get a list of names that end with “A”, we can use the same wild card, but we will have to put it in the front and the letter that we want to search for at the end, shown like the below.
SELECT * FROM #SQL_Like WHERE ContactName Like '%A'
And the result for the same would be
NameID ContactName
2 Anna
3 Brenda
7 Edna
Suppose, if there is a requirement where we know that we need to look into the names, in whose name the 2nd letter is “A”, then we have to write a query like the below, the “_” underscore wild card symbol denotes a single character.
SELECT * FROM #SQL_Like WHERE ContactName Like '_A%'
And the result for the same would be
NameID ContactName
4 Catherine
5 Daniel
10 Hannah
11 Ian
Say, if we want to search for a name, which starts with “A” and in which the second alphabet is something between “A” and “M”, then our result has to be the first name that we had inserted, i.e “Amy”. For this we actually use the “[ ]” square brackets, they are used for searching any single character within the specified range ([a-g]) or set ([abcdefg]).
SELECT * FROM #SQL_Like WHERE ContactName Like A[A-M]%'
Using SQL NOT Like
Till now we have seen how to directly use the “Like operator”. Now we shall take up an example where we will add “Not” in front of our “Like”. For this, the example that we can take is, say we need a list of all the name’s that do not have the alphabet “A” in them, then our query would resemble the below.
SELECT * FROM #SQL_Like WHERE ContactName NOT Like '%A%'
Using SQL Like Escape Clause
Everything seems to be fine till now, so, let’s think like this, the wild card symbols are really cool, but what if I need to look into a string for those wild card character’s, puzzled!!!
For this we have these escape clause J. Check the syntax at the beginning of the article
In the below example, my escape character is “!”, so I am actually trying to search for John%, of course practically no names will exist in this way unless there is a type!!! This is just for the sake of taking an example and looking at on how to use it
SELECT * FROM #SQL_Like WHERE ContactName LIKE '%John!%%' ESCAPE '!'
Let’s take one more example, here we want to look for the underscore symbol, and that’s a wildcard symbol, so decide to make use of the Escape clause over here as well.
SELECT * FROM #SQL_Like WHERE ContactName LIKE '%John!_%' ESCAPE '!'
The basic thing over here is that we need to look for a symbol that can be used in the escape clause, means something, that does not exist in our string.
Using SQL Like with Variables and Expressions
We can use the SQL LIKE operator with Variables and Expressions, they can combination of alphabets, alphabets and alpha-numeric, a query with scalar output, a varchar or nvarchar type local variable. But, remember one thing, at the end, everything would be looked at as a string(Not char) by the sql server.
The below is a self-explanatory code snippet. Firstly, we are declaring a variable, then we are setting it to a string “la” and then we are searching for it in the names. In the first, example we are trying to search for characters in the start of the name and in the second example we are trying to search for the characters in the end of the name
DECLARE @Name nvarchar(2)
SET @Name ='Ia'
SELECT * FROM #SQL_Like WHERE ContactName Like @Name +'%'
DECLARE @Name nvarchar(2)
SET @Name ='N'
SELECT * FROM #SQL_Like WHERE ContactName Like '%'+ @Name
Using SQL Like with Case Statement
As stated earlier, the sql like condition returns a true or false, so we can use this in multiple scenarios in conjunction with a CASE statement. Here in the below code snippet we are actually trying to find all the names that start with A, B. We can also construct similar thing where the condition can equal to 0.
SELECT * FROM #SQL_Like
WHERE CASE
WHEN ContactName LIKE 'A%' THEN 1
WHEN ContactName LIKE 'B%' THEN 1
END = 1
Tagged as:
Syntax,
T-SQL
