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.

Syntax

    ►►─SUBSTR(─┬─<Field Reference>───┬─,─<Position>─,─<Length>─)───────►◄
               ├─<COL.nnn Reference>─┤
               └─<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.

See also topic: Rules for all Logic Text

Examples: SUBSTR function in ERF

Example logic text	Meaning
IF (SUBSTR({field1},1,3) = “ABC”)     THEN SELECT ENDIF	Select any input records where field1 has the characters ABC starting in position 1, and skip all other records. The code can also be written as:   SELECTIF(SUBSTR({field1},1,3) = “ABC”)
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 greater than column 3.
IF (SUBSTR({Cust_Code},3,2) = “ZZ”) THEN      COLUMN = {Business_name} ELSE      COLUMN = {Name} ENDIF	If the Cust_Code contains the characters ZZ at position 3for length 2 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.