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

substring('Hello, World',0,5)

Output: Returns the string: Hello.

substring(string_val,start_index,end_index)


ArgumentRequired?Data TypeDescription
string_valYstringString 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

string_val

String constant to be searched.

Data TypeRequired?Example Value
StringYes'This is my string.'

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

Transformation:

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

Since the string can be five or ten characters in length, you need to use the SUBSTRING function in the second transformation, too. If the data is limited to five-digit zip codes, you could use the RIGHT function.

Results:

LastNameZipCodesubstring_ZipCodesubstring_ZipCode2
Able941014101
Baker23502-11223502
Charlie368456845