Contents:
- A column reference can refer to a column of String, Object, or Array type, which makes the
FIND
function useful for filtering data before it has been completely un-nested into tabular data. - 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
MATCHES
function, which returns a true/false response. See MATCHES Function.
You can also search a string from the right. For more information, see RIGHTFIND 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:
find(MyName,'find this',true,0)
Output: Searches the MyName
column value for the string find this
from the beginning of the value, ignoring case. If a match is found, the index value where the string is located is returned.
String literal example:
find('Hello, World','lo',false,2)
Output: Searches the string Hello, World
for the string lo
, in a case-sensitive search, beginning at the third character in the string. Since the match is found at the fourth character, the value 3
is returned.
If example:
if(find(SearchPool,'FindIt') >= 0, 'found it', '')
Output: Searches the SearchPool
column value for the string FindIt
from the beginning of the value (default). Default behavior is to not ignore case. If the string is found, the value found it
is returned. Otherwise, the column is empty.
Syntax and Arguments
find(input_string,string_pattern,[ignore_case], [start_index])
Argument | Required? | Data Type | Description |
---|---|---|---|
input_string | Y | string | Name of the column, function returning a string, or string literal to be applied to the function |
string_pattern | Y | string | Name of column, function returning a string, or string literal or pattern to find |
ignore_case | N | boolean | If true , a case-insensitive match is performed. Default is false . |
start_index | N | integer (non-negative) | If specified, this value identifies the start index value of the source data to search for a match. If not specified, the entire string is searched. |
For more information on syntax standards, see Language Documentation Syntax Notes.
input_string
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.
- Function returning a string value.
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, function returning a string, or column reference (String, Array, or Object) | myColumn |
string_pattern
Column of strings, , function returning a string, string literal or pattern to find. An input value can be a literal, Pattern , or a regular expression.
- Missing string or column values generate the start-index parameter value.
- String literals must be quoted (
'Hello, World'
).
- String literals must be quoted (
- 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 FIND
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_index
The index of the character in the column or string literal value at which to begin the search. For example, a value of 2
instructs the FIND
function to begin searching from the third character in the column or string value.
NOTE: Index values begin at 0
. If not specified, the default value is 0
, which searches the entire string.
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 |
Tip: For additional examples, see Common Tasks.
Examples
Example - Locate product purchases in transaction stream
Source:
You have the simplified transaction stream listed below in which master information about a transaction (TransactionId
and CustomerId
) is paired with order detail information that is brought into the application as an array in the OrderDetail
column. The array column contains information about product ID, quantity, and the type of transaction.
TransactionId | CustomerId | OrderDetail |
---|---|---|
12312312 | 100023 | [{"ProdId":"54321","Qty":"5","TransType":"PURCHASE"}] |
12312313 | 100045 | [{"ProdId":"94105","Qty":"12","TransType":"PURCHASE"}] |
12312314 | 100066 | [{"ProdId":"54321","Qty":"1","TransType":"TEST"}] |
12312315 | 100068 | [{"ProdId":"85858","Qty":"9","TransType":"PURCHASE"}] |
The transaction stream includes test transactions, which are identified by the value TEST
for TransType
in the detail column. You want to remove these transactions early in the process, which should simplify your dataset and speed up its processing.
Transformation:
First, you must identify the records that contain the test transaction value. The following transform generates a new column containing true/false values for whether the value "TEST"
appears in the OrderDetail
transform.
Tip: You should include the double-quotes around the value, in case the other fields in the array could contain some version of the value TEST
. Note that the double quotes need to be escaped, as in the value below.
Transformation Name | New formula |
---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | find(OrderDetail, '\"TEST\"', false, 0) |
When the step is added to the recipe, the find_OrderDetail
column is generated, containing the index value returned by the FIND
function. In this case, there is only one row that contains a value: 42
for the third transaction.
You can then add the following step to keep the rows where the FIND
function returned a null value in the find_OrderDetail
column:
Transformation Name | Filter rows |
---|---|
Parameter: Condition | Custom formula |
Parameter: Type of formula | Custom single |
Parameter: Condition | isnull(find_OrderDetail) |
Parameter: Action | Keep matching rows |
Results:
TransactionId | CustomerId | OrderDetail | find_OrderDetail |
---|---|---|---|
12312312 | 100023 | [{"ProdId":"54321","Qty":"5","TransType":"PURCHASE"}] | |
12312313 | 100045 | [{"ProdId":"94105","Qty":"12","TransType":"PURCHASE"}] | |
12312315 | 100068 | [{"ProdId":"85858","Qty":"9","TransType":"PURCHASE"}] |
You can delete the find_OrderDetail
column at this time.
This page has no comments.