Matches some or all of a string, based on the user-defined starting and ending index values within the string.

Column reference example:

derive type:single value:SUBSTRING(MyName,0,5)

Output: The first five letters of the MyName column value is written to the new column. 

String literal example:

derive type:single value:SUBSTRING('Hello, World',0,5)

Output: The string Hello is written to the new column.

derive type:single value:SUBSTRING(column_string,start_index,end_index)

ArgumentRequired?Data TypeDescription
column_stringYstringName of the column or string literal to be applied to the function
start_indexYinteger (non-negative)Index value for the start character from the source column or value
end_indexYinteger (non-negative)Index value for the end character from the source column or value

column_string

Name of the column or string constant to be searched.

Data TypeRequired?Example Value
StringYesMyName

start_index

Index value of the character in the string to begin the substring match.

Data TypeRequired?Example Value
Integer (non-negative)Yes0

end_index

Index value of the character in the string that is one after the end the substring match.

Data TypeRequired?Example Value
Integer (non-negative)Yes5


Example - Sectional Information in Zipcodes

Source:

A US zip code contains five digits with an optional Zip+4 extension consisting of four digits. Valid zip code values can be a mixture of these formats.

Within zip code values, each digit has significance:

Here is some example data: 

LastNameZipCode
Able94101
Baker23502-1122
Charlie36845

Transform:

You are interested in the region and area or town identifiers within a zip code region. You can use the following transforms applied to the ZipCode column to extract this information:

derive type:single value:SUBSTRING(ZipCode,1,3)

derive type:single value:SUBSTRING(ZipCode,3,5)

 

Results:

LastNameZipCodesubstring_ZipCodesubstring_ZipCode2
Able941014101
Baker23502-11223502
Charlie368456845