Wildcard for numbers not characters in Beast Mode or Magic ETL?

celiac

I'm trying to create a new column based on the prefix of another column, but the prefix B### is categorized as type B, and prefixes BC and BW are categorized as another type H.

E.g.

Col_ori	Col_new
B001	B
BC002	H
BW001	H

The wildcard % (i.e. using LIKE 'B%') will include all prefixes starting with B. How can I distinguish these prefixes?

GrantSmith

You'll need to start specific and then work your way down the list to be more generic with a case statement that way the more specific (BC or BW in your case) will be evaluated first.

CASE WHEN `Col_ori` LIKE 'BC%' THEN 'H'
WHEN `Col_ori` LIKE 'BW%' THEN 'H'
WHEN `Col_ori` LIKE 'B%' THEN 'B'
END

GrantSmith

You'll need to start specific and then work your way down the list to be more generic with a case statement that way the more specific (BC or BW in your case) will be evaluated first.

CASE WHEN `Col_ori` LIKE 'BC%' THEN 'H'
WHEN `Col_ori` LIKE 'BW%' THEN 'H'
WHEN `Col_ori` LIKE 'B%' THEN 'B'
END