FirebirdSQL logo
STARTING WITH
Syntax
<value> [NOT] STARTING WITH <value>

The STARTING WITH predicate searches for a string or a string-like type that starts with the characters in its value argument.The case- and accent-sensitivity of STARTING WITH depends on the collation of the first value.

When STARTING WITH is used in the search conditions of DML queries, the Firebird optimizer can use an index on the searched column, if it exists.

Example

Search for employees whose last names start with “Jo”:

SELECT LAST_NAME, FIRST_NAME
FROM EMPLOYEE
WHERE LAST_NAME STARTING WITH 'Jo'
See also

LIKE

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.