This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
dev:sql:functions:substring [2009/12/06 22:35] tdo |
dev:sql:functions:substring [2016/06/28 22:38] (current) |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| {{page>:top_add&nofooter&noeditbtn}} | {{page>:top_add&nofooter&noeditbtn}} | ||
| - | <html><div align="center"><span style="color:red">DRAFT</span></div></html> | + | ====== Development: Omnidex SQL ====== |
| - | ====== Omnidex SQL Function: SUBSTRING ====== | + | ===== SQL Functions: SUBSTRING ===== |
| - | {{page>:sql_bar&nofooter&noeditbtn}} | + | ==== Description ==== |
| - | ===== 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 SUBSTRING function returns a new character string that is a portion of another column or string. | + | |
| - | The new string begins at the specified starting point in the original column or string 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. | + | This function returns a STRING datatype. |
| - | The return data type is C STRING. | + | ==== Syntax ==== |
| - | ===== Syntax ===== | + | SUBSTRING( character_expression FROM numeric_expression [ FOR numeric_expression ] ) |
| - | SUBSTRING( <column_spec | 'string' FROM start_no [ FOR length ])) | + | |
| - | == < column_spec | 'string' >== | + | == character_expression == |
| - | Required. This is the optionally database.table qualified column or string literal enclosed in single quotes that contains the characters that will be returned. | + | 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. |
| - | == FROM start_no == | + | == numeric_expression == |
| - | Required. The FROM start_no is a number indicating the number of characters where in the column or string to start the extraction of the partial string. The start_no | + | Any expression that returns a numeric_class datatype to be used as either the offset or the length. |
| - | ^ String ^ | M | y | | s | r | i | n | g | | + | The FROM //numeric_expression// indicates the number of characters from the start of the //character_expression// to start the extraction of the partial string. |
| - | ^Position ^ | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | | + | |
| - | == FOR length == | + | ^ String| M | y | | s | t | r | i | n | g | |
| + | ^ Position| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | | ||
| - | Optional. This indicates the number of characters after and including the start character, that will be returned. If omitted, all of the remaining characters after and including the start character will be returned. | + | If the start_no was 4 in the string above, then 's' would be the first character of the returned substring. |
| - | ===== Example ===== | + | ==== Example ==== |
| - | ==== Example 1 ==== | + | === Example 1: Substring of column === |
| - | <code SQL> | + | <code> |
| - | select product_no, | + | > select name, |
| - | SUBSTRING(PRODUCTS.PRODUCT_NO FROM 2 FOR 3) as Sub1, | + | >> substring(phone from 2 for 3) area_code, |
| - | SUBSTRING(PRODUCTS.PRODUCT_NO FROM 5) as Sub2 | + | >> substring(phone from 7 for 8) phone_number |
| - | from products | + | >> from individuals; |
| - | Product_No Sub1 Sub2 | + | 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 | ||
| - | ASUP93541 SUP 93541 | + | </code> |
| - | BPRI54687 PRI 54687 | + | === Example 2: Complex expression === |
| + | <code> | ||
| + | The following example removes the salutation from the name. | ||
| + | <code> | ||
| + | > 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> | </code> | ||
| + | ====== Additional Resources ====== | ||
| + | |||
| + | See also: | ||
| + | {{page>dev:sql:functions:see_also_string&nofooter&noeditbtn&noheader}} | ||
| {{page>:bottom_add&nofooter&noeditbtn}} | {{page>:bottom_add&nofooter&noeditbtn}} | ||
| + | |||
| + | |||
| + | |||