Contains
Contains is a way of testing whether part of your string matches some specified text, which can be useful if you want to search for or extract key words or perform some kind of fuzzy matching (comparing similar but not identical values).
NB. Contains
functions are case sensitive, so transforming the value being checked to upper/lower case before searching is a good way of avoiding incorrect results because of inconsistencies in case. All below examples are transformed to upper case for this reason.
Excel
Excel does not have a specific CONTAINS
function. Instead, use FIND
to return the index of a string if found, or throw an error if the string is not found. You can then use NOT(ISERR())
to check that the result of FIND
is an error; if the result is TRUE
, the cell contains the target string:
=~NOT!~(~ISERR!~(~FIND!~(‹target_string›, ‹within_string›)))
=NOT(ISERR(FIND("MANAGE",UPPER(A2))))
Tableau
~CONTAINS!~(‹within_string›, ‹target_string›)
CONTAINS(UPPER([Role]), "MANAGE")
Alteryx
~CONTAINS!~(‹within_string›, ‹target_string›«, case_sensitive»)
CONTAINS([Role], "MANAGE") // case insensitive
CONTAINS([Role], "Manage", 0) // case sensitive
NB. Omitting case_sensitive
or setting it to 1
means that CONTAINS()
will ignore case. Including a 0
will make it respect case.
OrgVue
OrgVue does not have a specific contains function. Instead, use indexOf()
, to return the index of a string if found, or -1
if the string is not found. You can use an if statement to check whether the result is > -1 and use this a proxy for testing whether a cell contains the target string:
‹collection›.value.~indexOf!~(‹target_string›) > -1
node.role.value.toUpperCase().indexOf('MANAGE') > -1
// Returns True|False
['UK', 'France', 'Germany', 'Spain', 'Italy'].indexOf(node.country) > -1 ? "Europe" : "Other"
// If node.country is one of the 5 countries in the array, return "Europe",
// else return "Other"
tSQL
~CONTAINS!~(‹within_string›, ‹target_string›)
SELECT *
FROM EmployeeData
WHERE CONTAINS(Role, "MANAGE")
NB. Case and punctuation are ignored. For more information about the behaviour of CONTAINS
, see: https://msdn.microsoft.com/en-us/library/ms187787.aspx.
Python
‹target_string› ~in!~ ‹within_string›
"MANAGE" in role.upper()
"Europe" if country in ['UK', 'France', 'Germany', 'Spain', 'Italy'] else "Other"
# If the variable country is one of the 5 countries in the array, return "Europe",
# else return "Other"
NB. The above example assumes a variable has been declared for role
. For more information, go to General > Variables.
Last updated
Was this helpful?