Overview | Syntax | Customization | Examples

The $CONTAINS function expands the search capabilities of SQL to include more flexible textual searches.

Most SQL statements are written with criteria in simple predicates, such as: FIRST_NAME = ‘William’. This predicate compares the entire contents of FIRST_NAME against the criteria ‘William’, providing no opportunities for more flexible searches. If the criterion does not match the column exactly, then the row will not be included. Given the likelihood of everything from nicknames to misspellings, this means that a lot of rows are “lost”, never to be seen by the user despite their importance and relevance.

SQL does provide a LIKE operator that allows exact substring searches and pattern-match searches. But this is very limited, and does not accommodate nicknames and misspellings. Furthermore, the LIKE operator has no awareness of the parsed words in a field. LIKE’s substring searches would find the word “LACE” in “WALLACE”, and the word “OLD” in “REYNOLDS” despite their lack of relevance.

There is no standardized SQL to support textual searches involving keyword searches, proximity searches, synonym searches, spellchecking and similar features. Some database vendors have implemented their own independent approaches, but they vary between databases.

This function returns a BOOLEAN datatype and can only be referenced in the WHERE clause of a SELECT statement.

CHAR[ACTER]_LENGTH ( character_expression )

Required. Any expression that returns a character-class datatype. Examples of expressions are columns, concatenations of columns, literal strings and functions that return a character datatype.

> select name, character_length(name) len from individuals;

NAME                                                LEN
--------------------------------------------------  -----------
MS. MARY LEWIS                                               14
MRS. ALICE R TOLLIVER                                        21
MR. SIDNEY K TOLLIVER                                        21
MRS. JENNIFER J HOPKINS                                      23
MR. CHARLIE HOPKINS                                          19
MS. KIMBERLY BLUE                                            17
KATHLEEN TUCKER                                              15

> select gender, name, char_length(gender || name) len from individuals;

G  NAME                                                LEN
-  --------------------------------------------------  -----------
F  MS. MARY LEWIS                                               15
F  MRS. ALICE R TOLLIVER                                        22
M  MR. SIDNEY K TOLLIVER                                        22
F  MRS. JENNIFER J HOPKINS                                      24
M  MR. CHARLIE HOPKINS                                          20
F  MS. KIMBERLY BLUE                                            18
F  KATHLEEN TUCKER                                              16

Next

See also: