RIGHT Function

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.

Since theRIGHTfunction 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.
TheRIGHTfunction requires an integer value for the number of characters to match. If you need to match strings using patterns, you should use theENDSWITHtransform instead. See ENDSWITH Function.

Wrangle vs. SQL: This function is part of Wrangle, a proprietary data transformation language. Wrangle is not SQL. For more information, see Wrangle Language.

Basic Usage

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.

Syntax and Arguments

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

For more information on syntax standards, see Language Documentation Syntax Notes.

column_string

Name of the column or string constant to be searched.

Missing string or column values generate missing string results.
String constants must be quoted ('Hello, World').
Multiple columns and wildcards are not supported.

Usage Notes:

Required?	Data Type	Example Value
Yes	String literal or column reference	myColumn

end_count

Count of characters from the right end of the string to include in the match.

Value must a non-negative integer. If the value is 0, then the match fails for all strings.
If this value is greater than the length of the string, then the match is the entire string.
References to columns of integer data type are not supported.

Usage Notes:

Required?	Data Type	Example Value
Yes	Integer (non-negative)	5

Examples

Astuce

For additional examples, see Common Tasks.

Example - Parse segments of social security numbers

Social security numbers follow a regular format:

XXX-XX-XXXX

Each of the separate numeric groups corresponds to a specific meaning:

XXX - Area value that corresponds to a geographic location that surrounds the SSN applicant's address
XX - Group number identifies the order in which the numbers are assigned within an area
XXX - Serial number of the individual within the area and group groupings.
For more information, see http://www.usrecordsearch.com/ssn.htm.

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:

Transformation Name	`Replace text or pattern`
Parameter: Column	SocialNum
Parameter: Find	'-'
Parameter: Replace with	''
Parameter: Match all occurrences	true

Transformation Name	`Replace text or pattern`
Parameter: Column	SocialNum
Parameter: Find	' '
Parameter: Replace with	''
Parameter: Match all occurrences	true

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:

Transformation Name	`New formula`
Parameter: Formula type	Single row formula
Parameter: Formula	left(SocialNum, 3)
Parameter: New column name	'SSN_area'

Transformation Name	`New formula`
Parameter: Formula type	Single row formula
Parameter: Formula	substring(SocialNum, 3,5)
Parameter: New column name	'SSN_group'

Transformation Name	`New formula`
Parameter: Formula type	Single row formula
Parameter: Formula	right(SocialNum, 4)
Parameter: New column name	'SSN_serial'

If desired, you can re-order the three new columns and delete the source column:

Transformation Name	`Move columns`
Parameter: Column(s)	SSN_serial
Parameter: Option	After
Parameter: Column	SSN_area

Transformation Name	`Move columns`
Parameter: Column(s)	SSN_group
Parameter: Option	After
Parameter: Column	SSN_area

Transformation Name	`Delete columns`
Parameter: Columns	SocialNum
Parameter: Action	Delete selected columns

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

Dans cette section: