Data Vault SQL Reference

Back Next

LIKE Predicates

The LIKE condition specifies a test that involves pattern matching. You can use the LIKE predicate with placeholders.

The following text is an example of a query that uses the LIKE predicate:

Select * from employee where first_name like “John%”

The following image shows the syntax for a LIKE (pattern-match) predicate:

character value expression: The character value expression
parameter is compared to the pattern string. The expression can consist of a direct column reference, a string function, a string constant, or any combination of these using the concatenation operator ( || ).
pattern-string: All string-constant arguments must be enclosed by single quotation marks ( ' ' ). The underscore character ( _ ) is a wildcard character that matches any single character; the percent character (%) is a wildcard character that matches any number (zero or more) characters. Note that wildcard characters can only appear at the end of the string.
ESCAPE: The optional ESCAPE clause designates a special escape character, which if placed immediately before a percent (%) or underline ( _ ) character causes that percent or underline character to be interpreted literally as part of the pattern string.
escape character: An escape character must appear after the ESCAPE keyword in the statement, if the ESCAPE clause is used. The escape character can be any single character, enclosed by single quotation marks ( ' ' ). It is recommended that the chosen escape character not be a common string component (such as a letter or digit), or one of the wildcard characters (the percent or underline character), as the query expression might not produce the expected results.