SQL Like Statement

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.

1

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 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.

view sourceprint?

SELECT * FROM #SQL_Like

WHERE CASE

WHEN ContactName LIKE 'A%' THEN 1

WHEN ContactName LIKE 'B%' THEN 1

END = 1