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:33] tdo |
dev:sql:functions:substring [2012/10/26 14:28] (current) |
||
|---|---|---|---|
| Line 13: | Line 13: | ||
| == < column_spec | 'string' >== | == < column_spec | 'string' >== | ||
| - | Required. This is the optionally fully qualified column or string literal enclosed in single quotes that contains the characters that will be returned. | + | Required. This is the optionally database.table qualified column or string literal enclosed in single quotes that contains the characters that will be returned. |
| == FROM start_no == | == FROM start_no == | ||
| Line 19: | Line 19: | ||
| 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 | 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 | ||
| - | ^ String ^ M | y | | s | r | i | n | g | | + | ^ String ^ | M | y | | s | t | r | i | n | g | |
| - | ^Positino ^ 1 | 2 | 3 | 4 | 5 | 6 | 7 | | + | ^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. | ||
| == FOR length == | == FOR length == | ||
| - | 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 | + | 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. |
| ===== Example ===== | ===== Example ===== | ||
| - | ==== Example 1 ==== | + | ==== Example 1: Substring of column ==== |
| <code SQL> | <code SQL> | ||
| - | select product_no, | + | > select product_no, |
| SUBSTRING(PRODUCTS.PRODUCT_NO FROM 2 FOR 3) as Sub1, | SUBSTRING(PRODUCTS.PRODUCT_NO FROM 2 FOR 3) as Sub1, | ||
| SUBSTRING(PRODUCTS.PRODUCT_NO FROM 5) as Sub2 | SUBSTRING(PRODUCTS.PRODUCT_NO FROM 5) as Sub2 | ||
| Line 38: | Line 40: | ||
| ASUP93541 SUP 93541 | ASUP93541 SUP 93541 | ||
| BPRI54687 PRI 54687 | BPRI54687 PRI 54687 | ||
| + | </code> | ||
| + | ==== Example 2: Substring of 'string' ==== | ||
| + | <code SQL> | ||
| + | > SELECT SUBSTRING ('My String' from 4) from $omnidex | ||
| + | SUBSTR | ||
| + | ------ | ||
| + | String | ||
| </code> | </code> | ||
| {{page>:bottom_add&nofooter&noeditbtn}} | {{page>:bottom_add&nofooter&noeditbtn}} | ||