This shows you the differences between two versions of the page.
|
dev:sql:functions:contains:home [2011/01/23 23:18] els |
dev:sql:functions:contains:home [2016/06/28 22:38] |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | {{page>:top_add&nofooter&noeditbtn}} | ||
| - | ====== Development: Omnidex SQL ====== | ||
| - | |||
| - | ===== SQL Functions: $CONTAINS ====== | ||
| - | |||
| - | **[[dev:sql:functions:contains:home|Overview]]** | | ||
| - | [[dev:sql:functions:contains:syntax|Syntax]] | | ||
| - | [[dev:sql:functions:contains:Customization]] | | ||
| - | [[dev:sql:functions:contains:examples|Examples]] | ||
| - | |||
| - | ==== Description ==== | ||
| - | |||
| - | 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. | ||
| - | |||
| - | ===== Syntax ==== | ||
| - | CHAR[ACTER]_LENGTH ( character_expression ) | ||
| - | |||
| - | == 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. | ||
| - | |||
| - | ==== Examples ==== | ||
| - | === Example 1: Column === | ||
| - | <code> | ||
| - | > 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 | ||
| - | |||
| - | </code> | ||
| - | |||
| - | === Example 2: Character Expression === | ||
| - | <code> | ||
| - | > 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 | ||
| - | |||
| - | </code> | ||
| - | |||
| - | ==== ==== | ||
| - | |||
| - | **[[dev:sql:functions:contains:syntax|Next]]** | ||
| - | |||
| - | ====== Additional Resources ====== | ||
| - | |||
| - | See also: | ||
| - | {{page>dev:sql:functions:see_also_string&nofooter&noeditbtn&noheader}} | ||
| - | |||
| - | {{page>:bottom_add&nofooter&noeditbtn}} | ||