RIGHTFIND Function
Returns the index value in the input string where the last instance of a matching string is located. Search is conducted right-to-left.
Input can be specified as a column reference or a string literal, although string literal usage is rare.
A column reference can refer to a column of String, Object, or Array type, which makes the
RIGHTFINDfunction useful for filtering data before it has been completely un-nested into tabular data.Starting location is specified from the end of the string.
Returned value is from the beginning of the string, regardless of the string index value.
If no match is found, the function returns a null value.
If you need to determine if a value is in an array or not, you can use the
MATCHESfunction, which returns a true/false response. See MATCHES Function.
You can also search a string from the left. For more information, see FIND 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:
rightfind(MyName,'find this',true,0)
Output: Searches the MyName column value for the last instance of the string find this from the end of the value, ignoring case. If a match is found, the index value from the beginning of the string is returned.
String literal example:
rightfind('Hello, World','lo',false,2)
Output: Searches the string Hello, World for the string lo, in a case-sensitive search from the third-to-last character of the string. Since the match is found at the fourth character from the left, the value 3 is returned.
If example:
if(rightfind(SearchPool,'FindIt') >= 0, 'found it', '')
Output: Searches the SearchPool column value for the string FindIt from the end of the value (default). Default behavior is to not ignore case. If the string is found, the value found it is returned. Otherwise, the value is empty.
Syntax and Arguments
rightfind(string_to_search,search_for,[ignore_case], [start_at])
Argument | Required? | Data Type | Description |
|---|---|---|---|
string_to_search | Y | string | Name of the string, function returning a string, or a column containing strings. |
search_for | Y | string | The string or pattern you want to search. This can be a string, function returning a string, or string literal or Wrangle pattern or regular expression. |
ignore_case | N | boolean | Indicates if the |
start_at | N | integer (non-negative) | Indicates the position in the column or string literal value at which to begin the search. This value can be an integer, a function returning an integer, or a column containing integers. The default value is 0. If not specified, the entire string is searched. |
For more information on syntax standards, see Language Documentation Syntax Notes.
string_to_search
Name of the item to be searched. Valid values can be:
String literals must be quoted (
'Hello, World').column reference to any type that can be inferred as a string, which encompasses all values.
Missing values generate the start-index parameter value.
Multiple values and wildcards are not supported.
Usage Notes:
Required? | Data Type | Example Value |
|---|---|---|
Yes | String literal or column reference (String, Array, or Object) | myColumn |
search_for
String literal or pattern to find. This value can be a string literal, a Wrangle , or a regular expression.
Missing string or column values generate the start-index parameter value.
String literals must be quoted (
'Hello, World').
Multiple values and wildcards are not supported.
Usage Notes:
Required? | Data Type | Example Value |
|---|---|---|
Yes | String literal or pattern | 'Hello' |
ignore_case
If true, the RIGHTFIND function ignores case when trying to match the string literal or pattern value.
Default value is false, which means that case-sensitive matching is performed by default.
Usage Notes:
Required? | Data Type | Example Value |
|---|---|---|
No | Boolean | true |
start_at
The index of the character in the column or string literal value at which to begin the search, from the end of the string. For example, a value of 2 instructs the RIGHTFIND function to begin searching from the third character in the column or string value.
Nota
Index values begin at 0. If not specified, the default value is 0, which searches the entire string from the end of the string.
Value can be an integer, a function returning an integer, or a column containing integers.
If a column name is specified, start_at can be different for each row.
If a constant integer value is specified, the start_at is same for all rows.
Value must be a non-negative integer value.
If this value is greater than the length of the string, then no match is possible.
Usage Notes:
Required? | Data Type | Example Value |
|---|---|---|
No | Integer (non-negative) | 2 |
Examples
Suggerimento
For additional examples, see Common Tasks.
Example - Locate filenames in a URL
In this example, you must extract filenames from a column of URL values. Some rows do not have filenames, and there is some variation in the structure of the URLs.
Source:
URL |
|---|
www.example.com |
http://www.example.com |
http://www.example.com/test_app |
http://www.example.com/index.html |
http://www.example.com/resources/mypic.jpg |
http://www.example.com/pages/mypage.html |
http://www.example.com/resources/styles.css |
www.example.com/resources/styles.css |
Transformation:
To preserve the original column, you can use the following to create a working version of the source:
Transformation Name | |
|---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | URL |
Parameter: New column name | 'filename' |
You can use the following to standardize the formatting of the working column:
Transformation Name | |
|---|---|
Parameter: Column | filename |
Parameter: Find | 'http:' |
Parameter: Replace with | '' |
Parameter: Ignore case | true |
Suggerimento
You may need to modify the above to use a Wrangle to also remove https://.
The next two steps calculate where in the filename values the forward slash and dot values are located, if at all:
Transformation Name | |
|---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | rightfind(filename,"\/",true,0) |
Parameter: New column name | 'rightFindSlash' |
Transformation Name | |
|---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | rightfind(filename,".",true,0) |
Parameter: New column name | 'rightFindDot' |
If either of the above values is 0, then there is no filename present:
Transformation Name | |
|---|---|
Parameter: Columns | filename |
Parameter: Formula | if((rightFindSlash == 0) || (rightFindDot == 0), '', right(filename,(len(filename)-rightFindSlash))) |
Results:
After removing the intermediate columns, you should end up with something like the following:
URL | filename |
|---|---|
www.example.com | |
http://www.example.com | |
http://www.example.com/test_app | |
http://www.example.com/index.html | index.html |
http://www.example.com/resources/mypic.jpg | mypic.jpg |
http://www.example.com/pages/mypage.html | mypage.html |
http://www.example.com/resources/styles.css | styles.css |
www.example.com/resources/styles.css | styles.css |