Matches some or all of a string, based on the user-defined starting and ending index values within the string. |
SUBSTRING
function matches based on fixed numeric values, changes to the length or structure of a data field can cause your recipe to fail to properly execute.SUBSTRING
function requires numerical values for the starting and ending values. If you need to match strings using patterns, you should use the extract
transform instead. See Extract Transform.substring('Hello, World',0,5) |
Output: Returns the string: Hello
.
substring(string_val,start_index,end_index) |
Argument | Required? | Data Type | Description |
---|---|---|---|
string_val | Y | string | String literal to be applied to the function |
start_index | Y | integer (non-negative) | Index value for the start character from the source column or value |
end_index | Y | integer (non-negative) | Index value for the end character from the source column or value |
String constant to be searched.
'Hello, World'
).Data Type | Required? | Example Value |
---|---|---|
String | Yes | 'This is my string.' |
Index value of the character in the string to begin the substring match.
0
.end_index
.Data Type | Required? | Example Value |
---|---|---|
Integer (non-negative) | Yes | 0 |
Index value of the character in the string that is one after the end the substring match.
start_index
.Data Type | Required? | Example Value |
---|---|---|
Integer (non-negative) | Yes | 5 |
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:
LastName | ZipCode |
---|---|
Able | 94101 |
Baker | 23502-1122 |
Charlie | 36845 |
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:
LastName | ZipCode | substring_ZipCode | substring_ZipCode2 |
---|---|---|---|
Able | 94101 | 41 | 01 |
Baker | 23502-1122 | 35 | 02 |
Charlie | 36845 | 68 | 45 |