Release 10.1A: OpenEdge Data Management:
SQL Reference

> ## SUBSTR

> Returns the substring of the character string corresponding to the first argument starting at `start_pos` and `length` characters long. If the third argument `length` is not specified, the substring starting at `start_pos` up to the end of `char_expression` is returned.

> #### Syntax

>     SUBSTR ( char_expression, start_pos [ , length ] )

> Example

> This example illustrates the `SUBSTR` function:

>     SELECT last_name, '(', SUBSTR (phone, 1, 3) , ')',
>               SUBSTR (phone, 4, 3), '-',
>               SUBSTR (phone, 7, 4)
>       FROM customer ;

> #### Notes

> - The first argument must be of type `CHARACTER`. It can be any meaningful character value (for example, a literal expression, database column, or parameter).

> - The second argument must be of type `INTEGER`. It indicates the starting position from which the substring result is extracted.

> - The third argument, if specified, must be of type `INTEGER`. It indicates the number of characters the substring function will extract.

> - The values for specifying position in the character string start from `1`. The first character in a string is at position `1`, the second character is at position `2`, and so on.

> - The result is of type `CHARACTER`.

> - If any of the argument expressions evaluate to `NULL`, the result is `NULL`.

> - `char_expression` and the result can contain multi-byte characters.

> - If the value of `start_pos` is:

> - Smaller than 0, the function returns a "Bad argument" error.
> - Bigger than the actual length of the string value, the function returns an empty zero length substring.
> - If the value of `length` is:

> - Smaller than 0, the function returns a "Bad argument" error.
> - Bigger than the actual length of the substring (from `start_pos` to the end of the literal), the function returns the substring from `start_pos` to the end of the literal.
> - Bigger than the actual length of the substring (from `start_pos` up to the end of the column’s row data), the function returns the substring from the `start_pos` to the end of the column’s row data. The function returns this, even when the substring result exceeds the column’s SQL width.
> - Bigger than 0 and the column’s row data exceeds the column’s SQL width, the function returns the substring.
> - If `length` is not specified, the function returns the substring from `start_pos` to the end of the literal.

> Note: The function returns the end of the column’s row data if the length of the substring is not bigger than the column’s SQL width. Otherwise, the function returns the substring truncated to the column’s SQL width.

> ##### Compatibility

> Progress extension

Copyright © 2005 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095