Checking multiple values in a string

[SerSwagster]

Hi everyone,

I want to know if it is possible to check the presence of multiple "values" within a string.

This is my case. I have a column called COLORS filled with these records (each of them is a singular string):

green,blue

red,green

red,blue,yellow

yellow,blue,red

yellow,red,blue,green

green,red,blue

...

If I want to control that a specific color (e.g. green) is not present in the string, I do this:

if(not wildmatch(COLOR, '*green*'), 'check', 'ok')

What should I do to control multiple colors in the same string? I can't do like this ('*green,blue*') because sometimes the values are written in a different order and so the string is different.

Any suggestion?

[rdelagarde · Accepted solution]

Hi,

Just like that ?

if(not wildmatch(COLOR, '*green*', '*blue*', '*red*'), 'check', 'ok')

[SerSwagster]

Hi,

this code means that all the values are linked by an AND operator or OR operator?

[tresesco]

It is OR, and that probably what you need here. 

[SerSwagster]

And in the opposite case, without NOT, that is

if(wildmatch(COLOR, '*green*'), 'check', 'ok')

How can achieve an AND solution? If i want to select all the records matching different parameters (e.g. matching green, blue and red)?

[rdelagarde]

Probably not the best solution but it works for an AND solution:

if(WildMatch(COLOR, '*red*'), 
  if(WildMatch(COLOR, '*green*'), 
    if(WildMatch(COLOR, '*blue*'), 'check', 'ok'),
  'ok'),
'ok')

[rwunderlich · Accepted solution]

I would find it a bit easier to read as 

if(
WildMatch(COLOR, '*red*') and WildMatch(COLOR, '*green*') and WildMatch(COLOR, '*blue*')
, 'check', 'ok')

-Rob

[SerSwagster]

OK Thanks. One last question: In the field COLOR, alongside values like yellow, green, etc., I have also more complex values like yellow-orange, green-blue etc. Records in the field COLOR are of this kind:

green,blue

red,green-blue

red,blue,yellow-orange

yellow,blue,red

yellow-orange,red,blue,green

green,red,blue

...

I made this specific code to count the records that contain a specific color:

if(Left(NAME, 5) = '2019_' and WildMatch(COLOR,'*yellow-orange*','*green-blue*), 'check',
if(Left(NAME, 5) = '2022_' and WildMatch(COLOR,'*yellow*','*orange*','*green*','*red*','*blue*'), 'check', 'ok'))

The problem is that in the row with name 2022, I want to select only occurrences with yellow, orange, etc. values, not also yellow-orange! But wildmatch sees the yellow word and takes also records with yellow-orange value. How can I solve this last problem?

[rwunderlich]

Something along the lines of:

WildMatch(COLOR,'*yellow*','*orange*','*green*','*red*','*blue*')
and not WildMatch(COLOR,'*yellow-*'' , 'check', 'ok'))

[SerSwagster]

Thanks for your answer, but with your solution CHECK parameter has no results,  as if it is that the two conditions (Wildmatch and not Wildmatch) are in conflict.