SAND CDBMS SQL Reference Guide
Selection Criteria for WHERE and HAVING Clauses

 

Predicates


LIKE Predicate

The syntax for a LIKE (pattern-match) predicate appears below:


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 wild card character that matches any single character; the percent character (%) is a wild card character that matches any number (zero or more) characters.

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 wild card characters (the percent or underline character), as the query expression might not produce the expected results.


Description

A particular row satisfies the LIKE predicate if the value of the column specified as the character value expression preceding the LIKE predicate matches the pattern specified in the pattern-string argument. Otherwise, the row is disqualified. The following examples demonstrate pattern matching:

The LIKE predicate is satisfied in the following cases:
 

part LIKE 'Q%'
all part values beginning with Q.

part LIKE '%Q'
all part values ending with Q.

part LIKE 'Q_ _ _9'
all part values beginning with Q and having a 9 as the fifth and final character.

part LIKE '_ _6%'
all part values having a 6 as the third character.

part LIKE '%QQQ%'
all part values containing the string QQQ (for example, QQQAB, AQQQB, and ABQQQ).


ESCAPE character

If either the percent (%) character or underscore ( _ ) character is to be interpreted literally within the pattern-string argument of a LIKE predicate, an escape character must precede the percent character or underscore character within the string constant.

In order to define an escape character, the ESCAPE keyword and its character argument must follow the pattern-string argument within the LIKE predicate. The character specified for the character argument must precede occurrences of the percent character or underscore character within the pattern-string argument. Consider the following example:

desc LIKE '%40!%%' ESCAPE '!'

 This LIKE predicate is satisfied by rows containing the string 40% in the desc column.

 

SOUNDS LIKE

The SOUNDS LIKE predicate enables searching for a string pattern based on the way it sounds. This "phonetic" search allows users to perform queries using search terms whose precise spelling might be uncertain, or to find all strings that sound similar despite actual spelling (that is, homophones). For example, the search string "Dion" will match all strings that are spelled the same way, as well as the following similar-sounding strings:

The syntax for the SOUNDS LIKE predicate appears below:


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
The pattern-string parameter specifies the "sounds like" search string constant. This argument must be enclosed by single quotation marks ( ' ' ).


Example

SELECT e_no
  FROM employee
    WHERE lname SOUNDS LIKE 'Smith';

In this example, an e_no value is returned for each record having an lname string value that sounds like "Smith", which could include "Smyth", "Schmidt", or "Szmyt", in addition to "Smith".

 

SPELLED LIKE

The SPELLED LIKE predicate compares a search string against the values in a character value expression, matching those that are spelled similarly. Similarity in spelling is determined according to the notion of "edit distance", which is the minimum number of single character insertions, deletions, and substitutions required to transform one string into another. For example, the string "permmasivie" is separated from "permissive" by an edit distance of 3:

  1. Replace the second 'm' in "permmasivie" with 'i': "permiasivie".
  2. Replace ‘a’ with 's': "permissivie".
  3. Delete the third 'i': "permissive".

Note that edit distance comparisons detect only spelling differences between two strings, not phonetic similarity. The SPELLED LIKE search is therefore complementary to the SOUNDS LIKE search described above.

The syntax for the SPELLED LIKE predicate appears below:


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
The pattern-string parameter specifies the "spelled like" search string constant. This argument must be enclosed by single quotation marks ( ' ' ).

edit distance
The edit distance parameter must be an integer constant from 1 to 9 inclusive. This number specifies the maximum number of single character deviations from the pattern-string argument allowed to still be considered a match.

Example

In the following example, the SPELLED LIKE predicate is used to find misspelled variants of the streetname value 'Sherbrooke' in the montreal table:

SELECT streetname
  FROM montreal
    WHERE streetname SPELLED LIKE 'Sherbrooke' WITHIN 2
    AND streetname != 'Sherbrooke';

This query will return such values as 'Sherbroke', 'Shebooke', and 'Shebrook', which can then be corrected.

 

Other Predicates: