Table of Contents

Search

  1. Preface
  2. The Transformation Language
  3. Constants
  4. Operators
  5. Variables
  6. Dates
  7. Functions
  8. Creating Custom Functions
  9. Custom Function API Reference

Transformation Language Reference

Transformation Language Reference

SUBSTR

SUBSTR

Returns a portion of a string. SUBSTR counts all characters, including blanks, starting at the beginning of the string.

Syntax

SUBSTR(
string
,
start
[,
length
] )
The following table describes the arguments for this command:
Argument
Required/
Optional
Description
string
Required
Must be a character string. Passes the strings you want to search. You can enter any valid transformation expression. If you pass a numeric value, the function converts it to a character string.
start
Required
Must be an integer. The position in the string where you want to start counting. You can enter any valid transformation expression. If the start position is a positive number, SUBSTR locates the start position by counting from the beginning of the string. If the start position is a negative number, SUBSTR locates the start position by counting from the end of the string. If the start position is 0, SUBSTR searches from the first character in the string.
length
Optional
Must be an integer greater than 0. The number of characters you want SUBSTR to return. You can enter any valid transformation expression. If you omit the length argument, SUBSTR returns all of the characters from the start position to the end of the string. If you pass a negative integer or 0, the function returns an empty string. If you pass a decimal, the function rounds it to the nearest integer value.

Return Value

String.
Empty string if you pass a negative or 0 length value.
NULL if a value passed to the function is NULL.

Examples

The following expressions return the area code for each row in the Phone port:
SUBSTR( PHONE, 0, 3 )
PHONE
RETURN VALUE
809-555-0269
809
357-687-6708
357
NULL
NULL
SUBSTR( PHONE, 1, 3 )
PHONE
RETURN VALUE
809-555-3915
809
357-687-6708
357
NULL
NULL
The following expressions return the phone number without the area code for each row in the Phone port:
SUBSTR( PHONE, 5, 8 )
PHONE
RETURN VALUE
808-555-0269
555-0269
809-555-3915
555-3915
357-687-6708
687-6708
NULL
NULL
You can also pass a negative start value to return the phone number for each row in the Phone port. The expression still reads the source string from left to right when returning the result of the
length
argument:
SUBSTR( PHONE, -8, 3 )
PHONE
RETURN VALUE
808-555-0269
555
809-555-3915
555
357-687-6708
687
NULL
NULL
You can nest INSTR in the
start
or
length
argument to search for a specific string and return its position.
The following expression evaluates a string, starting from the end of the string. The expression finds the last (right-most) space in the string and then returns all characters preceding it:
SUBSTR( CUST_NAME,1,INSTR( CUST_NAME,' ' ,-1,1 ) - 1 )
CUST_NAME
RETURN VALUE
PATRICIA JONES
PATRICIA
MARY ELLEN SHAH
MARY ELLEN
The following expression removes the character '#' from a string:
SUBSTR( CUST_ID, 1, INSTR(CUST_ID, '#')-1 ) || SUBSTR( CUST_ID, INSTR(CUST_ID, '#')+1 )
When the
length
argument is longer than the string, SUBSTR returns all the characters from the start position to the end of the string. Consider the following example:
SUBSTR('abcd', 2, 8)
The return value is ‘bcd’. Compare this result to the following example:
SUBSTR('abcd', -2, 8)
The return value is ‘cd’.