SQL LIKE Operator

SELECT <result_list> FROM <table_name> WHERE <column_name> [NOT] 
LIKE <column_value> [ESCAPE <char>]

Indexed Columns

If the left side of the LIKE operator is the name of an indexed column of type string, ActiveSpaces converts the LIKE operator into a range query using >= and <. This enables the ActiveSpaces index selection algorithm to select the index and use it for scanning rows when processing the query. For example, take a look at the following statement:

SELECT * FROM table1 WHERE lastname LIKE 'Long'

Internally, this statement is converted to the following statement:

SELECT * FROM table1 WHERE lastname >= 'LONG' AND lastname < 'lonh'

Even if we have an index on the column, using LIKE <column_value> results in a full table scan if the <column_value> is a string that starts with a wildcard (%, _) or digit, or if <column_value> is a long value.

Pattern Matching

The pattern matching is not case sensitive for upper or lower case ASCII characters. The pattern matching is case-sensitive for Unicode characters that are beyond the ASCII range.

SELECT * FROM table1 WHERE lastname LIKE 'long'

searches the lastname column for values of LONG, lONG, loNG, lonG, long, LoNG, LonG, and so on.

However, in case of Unicode characters, pattern matching is case sensitive. Consider the following statement:

SELECT * FROM table1 WHERE lastname LIKE 'Ünder'

This query searches the lastname column for values of ÜNDER, ÜNDEr, ÜNDer, ÜNder, Ünder, and so on. But, it does not search for lastname column values that start with üNDER, üNDer, and so on. Pattern matching, when searches are not case sensitive works according to the order of characters in the ASCII table. Upper-case characters sort before lower-case characters. In the ASCII table, A=65 and a=97. Therefore, 'A' sorts before 'a'. For example, if you have an index defined for lastname and run the following query:

SELECT * FROM table1 WHERE lastname LIKE 'm%'

The index is used but all the rows are scanned where the lastname value starts with "M" through "Z" and "a" through "m". It is important to be aware that using LIKE can end up scanning many more rows than you would expect because of how pattern matching works when searches are not case sensitive. Using wildcard characters as the first character makes it impossible to know where to start searching and as a result a full table scan must occur.

Wildcards

Two wildcard characters can be used in conjunction with the LIKE operator:

• % (percent) - matches 0, 1, or multiple characters
• _ (underscore) - matches a single character

The following statements are examples of Wildcards:

Wildcards	Finds Values That...
'a%'	start with 'a' or 'A'
'%a'	end with 'a' or 'A'
'a%o'	start with 'a' or 'A' and end in 'o' or 'O'
'a_'	start with 'a' or 'A' and are two characters long
'_a%'	have 'a' or 'A' as the second character
'%at%'	contain 'AT', 'At', or 'at'
'a_%_%'	start with 'a' or 'A' and are at least 3 characters long
'_____'	are exactly 5 characters long

Escape Character

If the character sequence being matched needs to include one of the wildcard characters as one of the characters to match, you can specify an escape character to use by specifying the optional ESCAPE clause after your LIKE pattern. For example, the following statement matches values of 100%:

SELECT * FROM table1 WHERE completed LIKE '100\%' ESCAPE '\'