This shows you the differences between two versions of the page.
|
dev:sql:functions:substring [2010/07/08 20:39] els |
dev:sql:functions:substring [2016/06/28 22:38] |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | {{page>:top_add&nofooter&noeditbtn}} | ||
| - | {{page>:sql_bar&nofooter&noeditbtn}} | ||
| - | ====== Omnidex SQL Function: SUBSTRING ====== | ||
| - | ===== Description ===== | ||
| - | The SUBSTRING function returns a new character string that is a portion of another character expression. The new string begins at the specified starting point in the original expression and extends the number of characters specified in the length parameter. If the length parameter is omitted, the new string extends to the end of the original string. | ||
| - | The return data type is C STRING. | ||
| - | ===== Syntax ===== | ||
| - | SUBSTRING( character_expression FROM numeric_expression [ FOR numeric_expression ] ) | ||
| - | |||
| - | == character_expression == | ||
| - | Required. Any expression that returns a character-class datatype to be used as the substring target. Examples of expressions are columns, concatenations of columns, literal strings and functions that return a character datatype. | ||
| - | |||
| - | == numeric_expression == | ||
| - | |||
| - | Any expression that returns a numeric_class datatype to be used as either the offset or the length. | ||
| - | |||
| - | The FROM //numeric_expression// indicates the number of characters from the start of the //character_expression// to start the extraction of the partial string. | ||
| - | |||
| - | ^ String| M | y | | s | t | r | i | n | g | | ||
| - | ^ Position| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | | ||
| - | |||
| - | If the start_no was 4 in the string above, then 's' would be the first character of the returned substring. | ||
| - | |||
| - | ===== Example ===== | ||
| - | ==== Example 1: Substring of column ==== | ||
| - | <code SQL> | ||
| - | > select name, | ||
| - | >> substring(phone from 2 for 3) area_code, | ||
| - | >> substring(phone from 7 for 8) phone_number | ||
| - | >> from individuals; | ||
| - | |||
| - | NAME ARE PHONE_NU | ||
| - | -------------------------------------------------- --- -------- | ||
| - | MS. MARY LEWIS 203 318-1659 | ||
| - | MRS. ALICE R TOLLIVER | ||
| - | MR. SIDNEY K TOLLIVER | ||
| - | MRS. JENNIFER J HOPKINS 860 998-2013 | ||
| - | MR. CHARLIE HOPKINS | ||
| - | MS. KIMBERLY BLUE | ||
| - | KATHLEEN TUCKER | ||
| - | |||
| - | </code> | ||
| - | ==== Example 2: Complex expression ==== | ||
| - | <code SQL> | ||
| - | The following example removes the salutation from the name. | ||
| - | |||
| - | <code SQL> | ||
| - | > select trim( | ||
| - | >> substring(NAME | ||
| - | >> from position('.' in NAME) + 1 | ||
| - | >> for $column_length(NAME) - position('.' in NAME))) nme | ||
| - | >> from INDIVIDUALS; | ||
| - | |||
| - | NME | ||
| - | -------------------------------------------------- | ||
| - | MARY LEWIS | ||
| - | ALICE R TOLLIVER | ||
| - | SIDNEY K TOLLIVER | ||
| - | JENNIFER J HOPKINS | ||
| - | CHARLIE HOPKINS | ||
| - | KIMBERLY BLUE | ||
| - | KATHLEEN TUCKER | ||
| - | </code> | ||
| - | {{page>:bottom_add&nofooter&noeditbtn}} | ||
| - | |||
| - | |||
| - | {{page>:top_add&nofooter&noeditbtn}} | ||
| - | {{page>:sql_bar&nofooter&noeditbtn}} | ||
| - | ====== Omnidex SQL Function: POSITION ====== | ||
| - | ===== Description ===== | ||
| - | The POSITION function finds the position of a search_string in a target_string. | ||
| - | |||
| - | If the target_string is of length zero, the result is 1. | ||
| - | |||
| - | If the search_string occurs as a substring within the target_string, the character position where the search_string first occurs is returned. If neither of these conditions is true, the result is 0. | ||
| - | |||
| - | The return data type is a 4-byte INTEGER. | ||
| - | |||
| - | String comparisons are case-sensitive. Use the UPPER and LOWER functions around the search_string and the target_string to produce a case-insensitive search. | ||
| - | |||
| - | ===== Syntax ===== | ||
| - | |||
| - | POSITION( search_expression IN target_expression) | ||
| - | |||
| - | == search_expression === | ||
| - | |||
| - | Any expression that returns a character-class datatype to be used as the search criteria. Examples of expressions are columns, concatenations of columns, literal strings and functions that return a character datatype. | ||
| - | |||
| - | == target_expression == | ||
| - | |||
| - | Any expression that returns a character-class datatype to be used as the search target. Examples of expressions are columns, concatenations of columns, literal strings and functions that return a character datatype. | ||
| - | |||
| - | ===== Example ===== | ||
| - | ==== Example 1: Column ==== | ||
| - | |||
| - | <code SQL> | ||
| - | > select name, position('.' in name) pos from individuals; | ||
| - | |||
| - | NAME POS | ||
| - | -------------------------------------------------- ----------- | ||
| - | MS. MARY LEWIS 3 | ||
| - | MRS. ALICE R TOLLIVER 4 | ||
| - | MR. SIDNEY K TOLLIVER 3 | ||
| - | MRS. JENNIFER J HOPKINS 4 | ||
| - | MR. CHARLIE HOPKINS 3 | ||
| - | MS. KIMBERLY BLUE 3 | ||
| - | KATHLEEN TUCKER 0 | ||
| - | |||
| - | </code> | ||
| - | |||
| - | ==== Example 2: Complex expression ==== | ||
| - | |||
| - | The following example removes the salutation from the name. | ||
| - | |||
| - | <code SQL> | ||
| - | > select trim( | ||
| - | >> substring(NAME | ||
| - | >> from position('.' in NAME) + 1 | ||
| - | >> for $column_length(NAME) - position('.' in NAME))) nme | ||
| - | >> from INDIVIDUALS; | ||
| - | |||
| - | NME | ||
| - | -------------------------------------------------- | ||
| - | MARY LEWIS | ||
| - | ALICE R TOLLIVER | ||
| - | SIDNEY K TOLLIVER | ||
| - | JENNIFER J HOPKINS | ||
| - | CHARLIE HOPKINS | ||
| - | KIMBERLY BLUE | ||
| - | KATHLEEN TUCKER | ||
| - | </code> | ||
| - | |||
| - | {{page>:bottom_add&nofooter&noeditbtn}} | ||