SUBSTR( ) function
Returns a specified substring from a string.
Syntax
SUBSTR(string, start, length)
Parameters
| Name | Type | Description |
|---|---|---|
| string | character | The value to extract the substring from. |
| start | numeric | The starting character position of the substring. |
| length | numeric |
The number of characters in the substring. If length is 0, the output is blank. |
Output
Character.
Examples
Basic examples
Literal character input
Returns "BCD":
SUBSTR("ABCDEF", 2, 3)
Returns "EF":
SUBSTR("ABCDEF", 5, 10)
Parsing structured character data
Returns "189543":
SUBSTR("***189543***", 4, 6)
Returns the four-digit year out of a character field containing dates formatted as "MM/DD/YYYY":
SUBSTR(DATE, 7, 4)
Advanced examples
Increasing field length
Use SUBSTR( ) to increase the length of a character field. Increasing the length of a field is a common harmonization task that you may need to perform before joining or appending two fields.
The example below pads the Product_Description field with blank spaces to create the computed field Product_Description_Long with a length of 50 characters.
DEFINE FIELD Product_Description_Long COMPUTED SUBSTR(Product_Description, 1, 50)
Remarks
How it works
The SUBSTR( ) function returns characters from the string value starting at the character position specified by start. The number of characters returned is specified by length.
How SUBSTR( ) handles spaces
Leading, trailing, or internal spaces in the string value are treated like characters. Spaces captured by start and length are included in the output string.
How padding works
If the length value exceeds the number of characters, including trailing spaces, from the start position to the end of string, the output string may or may not be padded on the right with spaces.
Padded with spaces
If you use SUBSTR( ) within a command that creates a field, the output is padded with spaces.
Padding when creating a computed field
Creates the computed field Product_Description_Long, with a length of 50 characters, based on the physical field Product_Description, with a length of 24 characters:
DEFINE FIELD Product_Description_Long COMPUTED SUBSTR(Product_Description, 1, 50)
Padding when extracting a physical field
Extracts the field Product_Description_Long, with a length of 50 characters, to a new table, based on the physical field Product_Description, with a length of 24 characters:
EXTRACT FIELDS SUBSTR(Product_Description, 1, 50) AS "Product_Description_Long" TO New_Table
Not padded with spaces
If you use SUBSTR( ) in a variable definition or an expression, the output is not padded with spaces.
No padding when defining a variable
Creates the variable v_prod_desc, with a length of 24 characters, based on the field length of Product_Description:
ASSIGN v_prod_desc = SUBSTR(Product_Description, 1, 50)
Note
Even though SUBSTR( ) specifies a length of 50 characters, the output is limited to the field length of Product_Description.
Related functions
SUBSTR( ) and SPLIT( ) both return a segment of data from a longer source string.
- SUBSTR( ) identifies the segment based on a numeric character position.
- SPLIT( ) identifies the segment based on a separator character.
ACL Scripting Guide 14.1