Working with Data: All the calculations you need t
  • Introduction
  • What is this book?
  • How to Use this Book
  • Document Notes
  • Authors' Thanks
  • Strings
    • Concatenate
    • Split
    • Length
    • Slice
    • Trim
    • Contains
    • Match
    • Replace
    • Upper/Lower Case
  • Numbers
    • Sum
    • Average
    • Roll Ups
    • Round
    • Floor
    • Power
    • Square Root
    • Absolute
  • Dates
    • Date Difference
    • Age
  • Logical
    • Logical Operators
    • If
    • Case / Switch
    • Is Null/Empty
  • General
    • Count
    • Filter
    • Sort
    • Lookup/Join
    • Variables
    • Convert Type
  • Appendix
    • DateTime Parts
    • Helpful Resources
Powered by GitBook
On this page
  • Excel
  • Tableau
  • Alteryx
  • OrgVue
  • tSQL
  • Python

Was this helpful?

  1. Strings

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")

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"
PreviousTrimNextMatch

Last updated 5 years ago

Was this helpful?

NB. Case and punctuation are ignored. For more information about the behaviour of CONTAINS, see: .

NB. The above example assumes a variable has been declared for role. For more information, go to .

https://msdn.microsoft.com/en-us/library/ms187787.aspx
General > Variables