Matches the right set of characters in a string, as specified by parameter. The string can be specified as a column reference or a string literal. |
RIGHT
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.RIGHT
function requires an integer value for the number of characters to match. If you need to match strings using patterns, you should use the ENDSWITH
transform instead. See ENDSWITH Function.Column reference example:
right(MyString,3) |
Output: Returns the rightmost (last) three letters of the MyName
column value.
String literal example:
right('Hello, World',5) |
Output: Returns the string: World
.
right(column_string,end_count) |
Argument | Required? | Data Type | Description |
---|---|---|---|
column_string | Y | string | Name of the column or string literal to be applied to the function |
end_count | Y | integer (positive) | Count of characters from the end of the source string to apply to the match |
Name of the column or string constant to be searched.
'Hello, World'
).Required? | Data Type | Example Value |
---|---|---|
Yes | String literal or column reference | myColumn |
Count of characters from the right end of the string to include in the match.
0
, then the match fails for all strings.Required? | Data Type | Example Value |
---|---|---|
Yes | Integer (non-negative) | 5 |
Social security numbers follow a regular format:
XXX-XX-XXXX |
Each of the separate numeric groups corresponds to a specific meaning:
Source:
You want to analyze some social security numbers for area, group, and serial information. However, your social security number data is messy:
NOTE: The following sample contains invalid social security numbers for privacy reasons. If you use this data in the application, it fails validation for the SSN data type. |
ParticipantId | SocialNum |
---|---|
1001 | 805-88-2013 |
1002 | 845221914 |
1003 | 865 22 9291 |
1004 | 892-732213 |
Transformation:
When the above data is imported, the SocialNum
column might or might not be inferred as SSN data type. Either way, you should clean up your data, using the following transforms:
At this point, your SocialNum
data should be inferred as SSN type and consistently formatted as a set of digits:
ParticipantId | SocialNum |
---|---|
1001 | 805882013 |
1002 | 845221914 |
1003 | 865229291 |
1004 | 892732213 |
From this more consistent data, you can now break out the area, group, and serial values from the column:
If desired, you can re-order the three new columns and delete the source column:
Results:
If you complete the previous transform steps, your data should look like the following:
ParticipantId | SSN_area | SSN_group | SSN_serial |
---|---|---|---|
1001 | 805 | 88 | 2013 |
1002 | 845 | 22 | 1914 |
1003 | 865 | 22 | 9291 |
1004 | 892 | 73 | 2213 |