SQL string comparison and LIKE operator

The original reference to this blog is from MySQL's online documentation. , msdn and another site.
There are three string comparison functions as defined in MySQL's documentation.

LIKE               Simple pattern matching
NOT LIKE     Negation of simple pattern matching
STRCMP()     Compare two strings



The description of this operator in msdn seemed  more readable, that's why I am using that in my next paragraphs. For complete reference, try one of these links.

LIKE

Determines whether or not a given character string matches a specified pattern. A pattern can include regular characters and wildcard characters.

Syntax

match_expression [ NOT ] LIKE pattern [ ESCAPE escape_character ]

Arguments

match_expression

Is any valid SQL Server expression of character string data type.

pattern

Is the pattern to search for in match_expression, and can include these valid SQL Server wildcard characters.

Wildcard character	Description	Example
%	Any string of zero or more characters.	WHERE title LIKE '%computer%' finds all book titles with the word 'computer' anywhere in the book title.
_ (underscore)	Any single character.	WHERE au_fname LIKE '_ean' finds all four-letter first names that end with ean (Dean, Sean, and so on).
[ ]	Any single character within the specified range ([a-f]) or set ([abcdef]).	WHERE au_lname LIKE '[C-P]arsen' finds author last names ending with arsen and beginning with any single character between C and P, for example Carsen, Larsen, Karsen, and so on.
[^]	Any single character not within the specified range ([^a-f]) or set ([^abcdef]).	WHERE au_lname LIKE 'de[^l]%' all author last names beginning with de and where the following letter is not l.

escape_character

Is any valid SQL Server expression of any of the data types of the character string data type category. escape_character has no default and must consist of only one character.

Result Types

Boolean

Result Value

LIKE returns TRUE if the match_expression matches the specified pattern.


That's enough of the theories, Now let's have a look at some examples.

Example Queries using % wildcard

The first example that we'll take a look at involves using % in the where clause of a select statement. We are going to try to find all of the suppliers whose name begins with 'Hew'.

SELECT * FROM suppliers
    WHERE supplier_name like 'Hew%';

You can also using the wildcard multiple times within the same string. For example,

SELECT * FROM suppliers
    WHERE supplier_name like '%bob%';

In this example, we are looking for all suppliers whose name contains the characters 'bob'.

You could also use the LIKE condition to find suppliers whose name does not start with 'T'. For example,

SELECT * FROM suppliers
    WHERE supplier_name not like 'T%';

By placing the not keyword in front of the LIKE condition, you are able to retrieve all suppliers whose name does not start with 'T'.

Example Queries using _ wildcard

Next, let's explain how the _ wildcard works. Remember that the _ is looking for only one character.
For example,

SELECT * FROM suppliers
    WHERE supplier_name like 'Sm_th';

This SQL statement would return all suppliers whose name is 5 characters long, where the first two characters is 'Sm' and the last two characters is 'th'. For example, it could return suppliers whose name is 'Smith', 'Smyth', 'Smath', 'Smeth', etc.

Here is another example,

SELECT * FROM suppliers
    WHERE account_number like '12317_';

You might find that you are looking for an account number, but you only have 5 of the 6 digits. The example above, would retrieve potentially 10 records back (where the missing value could equal anything from 0 to 9). For example, it could return suppliers whose account numbers are:

123170

123171

123172

123173

123174

123175

123176

123177

123178

123179.

Example Queries using Escape Characters

Next, in Oracle, let's say you wanted to search for a % or a _ character in a LIKE condition. You can do this using an Escape character.
Please note that you can define an escape character as a single character (length of 1) ONLY.
For example,

SELECT * FROM suppliers
    WHERE supplier_name LIKE '!%' escape '!';

This SQL statement identifies the ! character as an escape character. This statement will return all suppliers whose name is %.

Here is another more complicated example:

SELECT * FROM suppliers
    WHERE supplier_name LIKE 'H%!%' escape '!';

This example returns all suppliers whose name starts with H and ends in %. For example, it would return a value such as 'Hello%'.

You can also use the Escape character with the _ character. For example,

SELECT * FROM suppliers
    WHERE supplier_name LIKE 'H%!_' escape '!';

This example returns all suppliers whose name starts with H and ends in _. For example, it would return a value such as 'Hello_'.