Using a wildcard to capture multiple items in a field

[RCS]

Hi alteryx community,

Im having an issue writing an if statement using wildards. 

Lets say I have field entries with wave 1, wave 2, wave 3, wave etc.

If [Other] = "Wave*"

Then 1

Else 0

Endif

Im trying to use a wildcard or any other related function to capture anything that starts with "Wave" and end in a plethora of variable endings.

[NicoleJohnson · Accepted solution]

Could you use the formula Contains?

If Contains([Other],"Wave") Then 1 Else 0 Endif

...Alternatively, if Wave should always be the first word, you could use:

If Left([Other],4) = "Wave" Then 1 Else 0 Endif

... or play with RegEx!

RegEx_Match([Other],"Wave\s.*") <-- This should give you a 1 for a match without having to put it in an IF statement

Hopefully one of those does the trick! :)

NJ

[RCS]

thank you you are a superstar ༼ つ ◕_ ◕ ༽つ★★★★★

[RCS]

thank you you are a superstar ༼ つ ◕_ ◕ ༽つ★★★★★

---

@NicoleJohnson

[Claje]

I wanted to add on to Nicole's excellent answer by copying a post I made the other day regarding the "LIKE" keyword from languages like SQL, that I think can be helpful here.

In particular, the STARTSWITH() and ENDSWITH() functions are both really powerful and more flexible than LEFT([field],length,'searchstring') or RIGHT([field],length,'searchstring')

I'm going to use examples with a SQL syntax for comparison to give you all three versions of the "Like Operator" as I would recommend in Alteryx

LIKE 'abc-treatment%'  (start of string):

STARTSWITH([field],'abc-treatment')

LIKE '%abc-treatment%' (any part of string):

CONTAINS([field],'abc-treatment')

LIKE '%abc-treatment' (end of string):

ENDSWITH([field],'abc-treatment')

[Claje]

I'm going to use examples with a SQL syntax for comparison to give you all three versions of the "Like Operator" as I would recommend in Alteryx

LIKE 'abc-treatment%'  (start of string):

STARTSWITH([field],'abc-treatment')

LIKE '%abc-treatment%' (any part of string):

CONTAINS([field],'abc-treatment')

LIKE '%abc-treatment' (end of string):

ENDSWITH([field],'abc-treatment')

[NicoleJohnson]

@Claje - This is a SUPER helpful comparison, would give it 3 stars if I could :) I will definitely be using this in the future! Thanks for excellent tip!!

[RCS]

༼ つ ◕_ ◕ ༽つ☆☆☆☆☆ stars for you too

[GaryKennedy]

Hi Claje,

I am new to alteryx so apologies if this is a basic question.

How would you do the opposite for example:

[Field] NOT LIKE ('%reject%')
[Field] NOT LIKE  ('%deleted%')
[Field] NOT LIKE ('%Cancel%%')

Is there a way of excluding with the % wildcard?

Regards,

Gary 

[Claje]

Hi,

in the examples you provided I would use !CONTAINS()

here are some examples:

!CONTAINS([Field],'reject')

!CONTAINS([Field],'deleted')
!CONTAINS([Field],'Cancel')

CONTAINS() will return TRUE if the string is found in a field.  the exclamation point/bang (!) reverses this statement, so if the string is found, !CONTAINS() will return FALSE.

This is basically the same as saying DOES NOT CONTAIN.

Hope this helps!

[GaryKennedy]

Thanks! Perfect Solve! :)