Checks if a string value matches a given pattern. LIKE is easier than using regular expressions for simple pattern matching. Use the various regular expression SQL functions listed in the Other SQL Functions topic for regular expression pattern matching.
<value> LIKE <pattern>
The LIKE operator has these parts:
Use the LIKE operator to check if a string value matches a given pattern. If a value matches a pattern, the operator returns True; otherwise, it returns False.
The LIKE operator is really a simplified form of regular expressions.
_ Underscore character - matches any single character.
% Percent sign - matches any number of characters, including no characters.
\_ Escaped backslash character with underscore character - matches the underscore character.
\% Escaped backslash character with percent sign - matches the percent sign.
\\ Backslash character with backslash character - matches the backslash character.
Use any other character, like a letter or a digit, to match itself.
As with regular expressions, the pattern string it uses is part of the overall SQL query, so it has to conform to the rules for escaping special characters used in SQL in general, for example, within SQL functions. As a practical matter, that means when using strings for patterns, we must use two backslash characters to escape the underscore character, the percent sign character, or the backslash character itself. See the discussion in the Escaping Special Characters section below.
The LIKE operator is case-sensitive. To match values without case, convert both the string and the pattern to either upper or lower case with StringToUpperCase or StringToLowerCase functions.
If either the checked value or the pattern is NULL, the LIKE operator also returns NULL.
Sample expressions using the LIKE operator, with the value on the left what is returned by the evaluated value, a pattern string within single ' quotes on the right, and the value returned by each expression.
abbc LIKE 'a%' returns TRUE
abbc LIKE '_c' returns FALSE
abbc LIKE '%c' returns TRUE
abbc LIKE '%bb' returns FALSE
a_bc LIKE '%' returns TRUE
a_bc LIKE 'a_bc' returns TRUE (because _ matches any character)
a_bc LIKE 'a\\_bc' returns TRUE (because \_ matches _)
a_bc LIKE 'a__c' returns TRUE
ab\c LIKE '%\\%' returns FALSE
ab\c LIKE '%\\\\%' returns TRUE
Note that because a string literal is used as a pattern, any backslash character used as an escape must be doubled.
Use the LIKE operator to select all products the name of which starts with C:
SELECT * FROM [Products]
WHERE [Name] LIKE 'C%';
Use the LIKE operator to select all products the name of which ends with r:
SELECT * FROM [Products]
WHERE [Name] LIKE '%r';
Use the LIKE operator to select all products the name of which includes the word Anton (ignoring case):
SELECT * FROM [Products]
WHERE StringToLowerCase([Name]) LIKE '%anton%';
Using a table that contains the names of provinces in Mexico:
SELECT [NAME] FROM [Mexico Table]
WHERE [NAME] LIKE 'Dur%';
Returns Durango.
SELECT [NAME] FROM [Mexico Table]
WHERE [NAME] LIKE '%an%';
Returns Guanajuato, Michoacan de Ocampo, Yucatan, Quintana Roo, Durango and San Luis Potosi.
A string literal in SQL is a text value given between ' delimiters. For example, in the SQL fragment SELECT * WHERE [Name] = 'John' the value 'John' is a string literal. SQL has special rules for string literals that allow use of backslash \ characters to escape the following character. In string literals the backslash \ character escapes the following character. To use the literal text don't we would use 'don\'t' in a query. To enter the backslash character itself use two backslashes: \\ as in 'C:\\Windows\\System' to mean the literal text value C:\Windows\System.
Because string literals in SQL are processed to parse backslash characters, that can complicate the use of strings as arguments to SQL functions, as patterns in regular expressions, or as patterns when using the LIKE operator, where the use of the backslash character has special meaning. For example, the two character sequence \s in regular expressions matches any single whitespace character. However, a string consisting of '\s' in SQL simply means "the single, literal, lower-case character s." If that string is used as a pattern argument in a regular expression SQL function the pattern seen by the regular expression will be s not \s.
Similarly, the two character sequence \_ (a backslash followed by an underscore) used as a pattern for the LIKE operator means "the single, literal, underscore _ character", while a pattern that consists of a single underscore _ character means "match any single character." If we use the string '\_' as a pattern for the LIKE operator such as in WHEN [Name] LIKE '\_' the pattern seen by LIKE after the string is processed will be _ and not \_. The LIKE will match Name values consisting of any single character, and not only of a single underscore _ character.
Because strings are processed to handle use of a backspace character to designate the following character as a literal, we generally have to use two backslashes in string literals in three settings:
For example, SQL functions within Manifold which use regular expressions are passed those regular expressions as a string argument. Consider the SQL function
StringRegexpMatches(<string>, <regexp>, <flags>)
This function finds strings within the given field which match a given regular expression. If we have a table that lists the names of various food products within the field Name and we want to find all products with a name of Dutch Chocolate we could use the function with the following arguments:
StringRegexpMatches([Name], 'Dutch Chocolate', 'c')
Note that the regular expression Dutch Chocolate is enclosed in single quotes ' as any string argument would be. The 'c' flag at the end means that the regular expression match by the function will be case sensitive.
We could also write that same regular expression as Dutch\sChocolate using the regular expression \s escape sequence meaning a white space character such as a space or tab. But since SQL will first preprocess the string to respect the \ character as an escape, we should write the string used as an argument within the function as:
StringRegexpMatches([Name], 'Dutch\\sChocolate', 'c')
Note the use of two backslashes \\ instead of just one. Why two backslashes? Strings passed as arguments to functions are one of those contexts where the backslash character has special meaning. To transmit the backslash character used in the \s escape to the regexp processor used by the function we must escape the \ with another \ so it is treated as a literal within the string and not itself an escape character the string should utilize to modify what is sent into the function.
That behavior can add up to a lot of backslashes sometimes, especially when we want to look for backslash characters in a pattern, because the regexp engine itself uses the backslash character as a special escape character.
Suppose for example we wanted to find a product with a name of Sweet\Sour Tart. A regular expression that would find such a name would be Sweet\\Sour Tart, using two backslashes where the first backslash escapes the second one to mean literally a backslash character and not an escape character in a \S escape expression. That's OK and easy to understand, but if we want to pass that regular expression through a string argument into a function we will have to escape both of those backslashes as well, making for four backslash characters when we use the function:
StringRegexpMatches([Name], 'Sweet\\\\Sour Tart', 'c')
Mercifully, backslashes are not that common in most language text, URLs or path names in civilized operating systems so we probably will not be writing many regular expressions requiring four backslashes in a row; but we will need to get used to writing two backslashes when using string literals for patterns in regular expressions or using the LIKE operator.
For example, suppose we use the Select pane to find all products in a table with a name consisting of two words, such as Mint Tea or Malaysian Coffee. A regular expression to match such names would be
\S+\s\S+
The \S escape matches any character that is not white space. The + plus character is a repetition count that says to match any one or more of the preceding, which in this case means to match one or more characters that are not white space. The \s escape matches any white space character. Taken together the regular expression matches any product name made up of two words separated by a white space character where each word consists of one or more characters that are not white space.
To use the Select pane to find such matches, in the Template tab we would choose the template Text Matches Regexp and in the Pattern box we would enter:
'\\S+\\s\\S+'
To deconstruct the above we first note the expression is within single quote ' characters because it is a string that is passed to the template. However, because backslash characters have special meanings within strings as an escape to transmit those backslash characters as part of the regular expression and not have them stripped out as something which controls the string we escape each backslash character with another backslash character just in front of it.
The rule of thumb for using string literals as patterns in regular expression functions, with the LIKE operator, or as with regular expressions as patterns to Select pane templates: simply replace each backslash in the regular expression with two backslashes, and enclose the entire regular expression in single ' quote characters.
Important: The above discussion applies to string literals, that is, strings which appear within single quote ' delimiters which are used within SQL queries as function arguments or as pattern strings for LIKE operator. We have to double up backslash escape \ characters because of how strings are processed. But we do not have to do that if we are pulling values from a field.
For example, suppose we have a record where the [source] field contains the value abbc and the [pattern] field contains the value a\_bc and we use those fields in a LIKE construction such as
[source] LIKE [pattern]
That will evaluate to FALSE because when a value is taken from a field there is no processing of any backslash characters in the field. The two character sequence \_ in a LIKE pattern means a single, literal, underscore _ character, so for the LIKE operator SQL will see a pattern meaning an a followed by a backslash \ character followed by the underscore _ character followed by b and c,. That will match the text value a_bc but it does not match the abbc value in the [source] field.
However, suppose instead of referring to the contents of a field we specified the pattern in the LIKE construction using a string literal:
[source] LIKE 'a\_bc'
The string in the above will be preprocessed by SQL to interpret the backslash character as an instruction meaning to treat the character following the underscore _ character as a literal character. So for the LIKE operator SQL will see a pattern consisting of an a followed by the underscore _ character followed by b and c, that is, a_bc. That pattern matches any single character in the second character position along with the first a and the terminating bc. That pattern matches abbc and so the LIKE operator expression will evaluate to TRUE.
Manifold has introduced an innovation, the @'...' notation, which allows us to avoid using escape characters in a string literal. Whatever is within the single ' quote characters in the notation need not be escaped. A popular use of this notation is within template boxes to write regular expressions that are not cluttered with very many backslash characters. Examples using the above strings would be:
@'Sweet\Sour Tart'
and
@'\S+\s\S+'
Thanks go to David S for launching a thread in the georeference forum that identified the need for more detailed discussion of escape characters in this topic.