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