SQL SIMILAR TO Extended Comparison Operator

The SIMILAR TO extended comparison operator returns true or false depending on whether its pattern matches the given string. It is similar to LIKE, except that it interprets the pattern using the a regular expression.

The syntax is:

  Value [NOT] SIMILAR TO [Regular Expression]

Use the SIMILAR TO extended comparison operator to filter a table based on the matching of a field value to a regular expression.

Use NOT to return the converse of a SIMILAR TO comparison. SIMILAR TO can be used only with string or compatible data types such as memo fields.

Examples:

• Contain numbers (anywhere in string): SIMILAR TO '[0-9]'
• Contain numbers ONLY: SIMILAR TO '^[0-9]*$'
• Start with numbers: SIMILAR TO '^[0-9]'
• Contain alpha (anywhere in string): '[a-zA-Z]'
• Contain alpha ONLY: SIMILAR TO '^[a-zA-Z]*$'
• Contain a substring "SHEN" to return "ASHENG": SIMILAR TO '(SHEN)'
• Escaping characters to find customer "^TEST": SIMILAR TO '[\^]'

Note: For details of the Regular Expression syntax see the SQL:1999 Standard.