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:27] 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 ==== |
| + | SUBSTRING( character_expression FROM numeric_expression [ FOR numeric_expression ] ) | ||
| - | ===== Syntax ===== | + | == character_expression == |
| - | SUBSTRING( <column_spec | 'string' FROM start_no [ FOR length ])) | + | 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. |
| - | == < column_spec | 'string' >== | + | == numeric_expression == |
| - | Required. This is the string that contains the characters that will be returned. | + | Any expression that returns a numeric_class datatype to be used as either the offset or the length. |
| - | == FROM == | + | The FROM //numeric_expression// indicates the number of characters from the start of the //character_expression// to start the extraction of the partial string. |
| - | The FROM keyword is required. | + | ^ String| M | y | | s | t | r | i | n | g | |
| + | ^ Position| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | | ||
| - | == start_no == | + | If the start_no was 4 in the string above, then 's' would be the first character of the returned substring. |
| - | Required. start is an integer indicating the position of the first character relative to 1 that will be returned. | + | ==== Example ==== |
| + | === Example 1: Substring of column === | ||
| + | <code> | ||
| + | > select name, | ||
| + | >> substring(phone from 2 for 3) area_code, | ||
| + | >> substring(phone from 7 for 8) phone_number | ||
| + | >> from individuals; | ||
| - | == FOR length == | + | 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 | ||
| - | 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. | + | </code> |
| - | ===== Example ===== | + | === Example 2: Complex expression === |
| - | ==== Example 1 ==== | + | <code> |
| - | <code SQL> | + | The following example removes the salutation from the name. |
| - | select product_no, | + | |
| - | SUBSTRING(PRODUCTS.PRODUCT_NO FROM 2 FOR 3) as Sub1, | + | |
| - | SUBSTRING(PRODUCTS.PRODUCT_NO FROM 5) as Sub2 | + | |
| - | from products | + | |
| - | Product_No Sub1 Sub2 | + | <code> |
| - | + | > select trim( | |
| - | ASUP93541 SUP 93541 | + | >> substring(NAME |
| - | BPRI54687 PRI 54687 | + | >> 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}} | ||
| + | |||
| + | |||
| + | |||