Difference between In function and Contains function

[bryanmason19]

Hi All,

I am just looking for some general information on the difference between the In function and the Contain function. I use both frequently and sometimes one will work but not the other. Also, sometimes Contains will return an error stating that it requires 2 or 3 inputs- what exaclty does this mean? 

Thanks

[pcatterson]

I'm not familiar with an 'IN' function in Alteryx.  Is it part of some extended library of functions?

[scottj]

I usually look at is as Contains looks for the occurrence of a particular string within a string.  So Contains([variable], '123') matches '123456', but not '456789'..  I use the IN statement, mostly in the interface tools, to match several strings.  [variable] IN ('1','2','3') would match any of the options (1. 2, 3 or any mix of them), not look for all.

[Federica_FF · Accepted solution]

HI,

the IN functions is in the Operator group and it looks at the entire field. It's the same as using [ = OR ].

Le'ts say you have a field:

[Column 1]

1151

1

2222

2

3456

[Column 1] in (1,2)  is the same as [Column 1] = 1 or [Column 1] = 2
It looks for the number 1 or the number 2

[Column 1]

1151 --> False

1 --> True

2222 -- False

2 --> True

3456 --> False

If you're dealing with numbers. If you're dealing with strings,

[Column 1] in ("1", "2")  is the same as [Column 1] = "1" or [Column 1] = "2"

It looks for the string "1" or "2"

[Column 1]

1151 --> False

1 --> True

2222 -- False

2 --> True

3456 --> False

The contains function is in the String group and can only be used with strings, and it looks at any part of the strings, not the entire string:

Contains([Column 1], "1")
It checks if the character "1" is contained

[Column 1]

1151 --> True

1 --> True

2222 -- False

2 --> False

3456 --> False

Contains([Column 1], "1")  or Contains([Column 1], "2")
It checks if the character "1" or the character "2" is contained

[Column 1]

1151 --> True

1 --> True

2222 -- True

2 --> True

3456 --> False

Contains --> String group --> Only strings, looks for any part of the field

IN --> Operator group --> Both strings and numbers, looks for the entire field

[stef_bolo]

Hello Federica,

First of all, thank you for your explanations on the difference between contains and  in function. Very well explained!

I have another question regarding the Contains function. Do you know if we can use wildcards in Contains function?

I have this example:

If Contains([title], " Review") THEN
"1"
ELSEIF
Contains([title], "Profile") THEN
"2"
ELSEIF
Contains([title], "PEST.*") THEN
"3"
ELSE

"10"
ENDIF

I am asking myself if the wildcard works here because it seems that not. (the blue part of my formula). I want to find PEST or PESTLE.

I know that I could add another ELSEIF with the other version of PESTLE but I am really interested in finding a way of using wildcard because I need this for other cases.

Thank you for any help on this,

[SophiaF]

@stef_bolo - you can't use a wildcard, as anything between " " is treated as a literal string in the contain function (unlike the Regex function below), but luckily contains([title],"PEST") will match both PEST and PESTLE, or anything else with PEST in it, such as TEMPESTUOUS.

An alternative to Contains would be regex, in which you can use a wildcard to only match things such as PEST and PESTLE but not TEMPESTUOUS:

REGEX_Match([Field1], "PEST.*")

[resqui]

In addition to CONTAINS or IN function is it possible to use a wildcard in a function?   For example, If wanted to look for text that started with "primary", in an Access  query that would say "primary*".   This would return any text like primary-DR, primary-NAS, or primary-Thick.  However it would not return a record like iNAS-primary or Thin-primary.   Is this possible? 

[scottj]

I would suggest trying the Starts with function, StartsWith([Field], 'Primary'), or you could do a Left([Field], 7) = 'Primary'

If the search is in the middle of the string, you would need to do a Substring.