Function SUBSTR

TABLE OF CONTENTS

  1. How do I use SUBSTR?
  2. Syntax
  3. Rules for the syntax
  4. Examples: SUBSTR function in ERF
  5. Examples: SUBSTR function in ECL

How do I use SUBSTR?

The function SUBSTR is used to specify a portion of a string. It can be applied to alphanumeric source file fields, column references to alphanumeric columns, alphanumeric lookup fields, and alphanumeric constants. You specify the field name, column reference, or constant, and the starting position and the length.
It can be used in assignments, and in comparisons with the string comparison operators.

(Syntax Legend)

Syntax


    ►►─SUBSTR(─┬─<Field Reference>───┬─,─<Position>─,─<Length>─)───────►◄
               ├─<COL.nnn Reference>─┤
               └─<String>────────────┘

Function SUBSTR Field Ref

Function SUBSTR Lookup

Function SUBSTR COL

Function SUBSTR String

Rules for the syntax

The position and length are integers. They must reference bytes within the field or string referenced.

SUBSTR can only be used in Extract Record Filter (ERF) and Extract Column Logic (ECL) text.
If used in an Extract Record Filter (ERF), the Column references COL.nnn can not be used.

SUBSTR can be using in a comparison but only using the string comparison operators BEGINS_WITH, CONTAINS and ENDS_WITH.

See also topic: Rules for all Logic Text

Examples: SUBSTR function in ERF

Example logic text Meaning
IF (SUBSTR({field2},6,5) CONTAINS “AA”) THEN
    SELECT
ENDIF		 Select only records where field2 has the
characters “AA” somewhere in the five bytes starting at position 6.
This example can also be written:
  SELECTIF(SUBSTR({field2},6,5) contains “AA”)

Examples: SUBSTR function in ECL

Example logic text Meaning
COLUMN = SUBSTR({Cust_Code},2,4) Set the current column to the four bytes starting at position 2 of field Cust_Code.
COLUMN = SUBSTR(COL.3,4,8) Set the current column to the eight bytes starting at position 4 of column 3. This column must be after column 3.
IF (SUBSTR({Cust_Code},3,2) BEGINS_WITH “ZZ”) THEN
     COLUMN = {Business_name}
ELSE
     COLUMN = {Name}
ENDIF		 If the Cust_Code has the characters ZZ
at position 3
then set current column to Business_name,
otherwise set current column to Name.
IF (SUBSTR({Cust_Code},3,8) CONTAINS “AA”) THEN
    WRITE(SOURCE=VIEW,DEST=EXT=03)
ENDIF		 Write to extract 3 those records where
the Cust_code contains “AA” anywhere in the eight characters starting from position 3.