FirebirdSQL logo
CONTAINING
Syntax
<value> [NOT] CONTAINING <value>

The CONTAINING predicate searches for a string or a string-like type looking for the sequence of characters that matches its argument.It can be used for an alphanumeric (string-like) search on numbers and dates.A CONTAINING search is not case-sensitive.However, if an accent-sensitive collation is in use then the search will be accent-sensitive.

Examples
  1. Search for projects whose names contain the substring “Map”:

    SELECT *
    FROM PROJECT
    WHERE PROJ_NAME CONTAINING 'Map';

    Two rows with the names “AutoMap” and “MapBrowser port” are returned.

  2. Search for changes in salaries with the date containing number 84 (in this case, it means changes that took place in 1984):

    SELECT *
    FROM SALARY_HISTORY
    WHERE CHANGE_DATE CONTAINING 84;
See also

LIKE

SIMILAR TO
Syntax
string-expression [NOT] SIMILAR TO <pattern> [ESCAPE <escape-char>]

<pattern>     ::= an SQL regular expression
<escape-char> ::= a single character

SIMILAR TO matches a string against an SQL regular expression pattern.Unlike in some other languages, the pattern must match the entire string to succeed — matching a substring is not enough.If any operand is NULL, the result is NULL.Otherwise, the result is TRUE or FALSE.

If a literal pattern is used, and it doesn’t start with a wildcard or other special regex character, SIMILAR TO can use an index.

Escaping Special Characters

To match against a character that is special in regular expressions, that character has to be escaped.There is no default escape character;the user specifies one when needed:

'Peer (Poire)' similar to 'P[^ ]+ \(P[^ ]+\)' escape '\'    -- true
'Pera [Pear]'  similar to 'P[^ ]+ #[P[^ ]+#]' escape '#'    -- true
'Päron-äppledryck' similar to 'P%$-ä%' escape '$'           -- true
'Pärondryck' similar to 'P%--ä%' escape '-'                 -- false

The last line demonstrates that the escape character can also escape itself, if needed.

Syntax: SQL Regular Expressions

The following syntax defines the SQL regular expression format.It is a complete and correct top-down definition.It is also highly formal and long, and may be daunting to anyone who hasn’t already some experience with regular expressions (or with highly formal, rather long top-down definitions).Feel free to skip it and read the next section, Building Regular Expressions, which uses a bottom-up approach, aimed at the rest of us.

<regular expression> ::= <regular term> ['|' <regular term> ...]

<regular term> ::= <regular factor> ...

<regular factor> ::= <regular primary> [<quantifier>]

<quantifier> ::= ? | * | + | '{' <m> [,[<n>]] '}'

<m>, <n> ::= unsigned int, with <m> <= <n> if both present

<regular primary> ::=
    <character> | <character class> | %
  | (<regular expression>)

<character> ::= <escaped character> | <non-escaped character>

<escaped character> ::=
  <escape-char> <special character> | <escape-char> <escape-char>

<special character> ::= any of the characters []()|^-+*%_?{}

<non-escaped character> ::=
  any character that is not a <special character>
  and not equal to <escape-char> (if defined)

<character class> ::=
    '_' | '[' <member> ... ']' | '[^' <non-member> ... ']'
  | '[' <member> ... '^' <non-member> ... ']'

<member>, <non-member> ::= <character> | <range> | <predefined class>

<range> ::= <character>-<character>

<predefined class> ::= '[:' <predefined class name> ':]'

<predefined class name> ::=
  ALPHA | UPPER | LOWER | DIGIT | ALNUM | SPACE | WHITESPACE