Wildcard for numbers not characters in Beast Mode or Magic ETL?
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?
Tagged:
0
Best Answer
-
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
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**3
Answers
-
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
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**3
Categories
- All Categories
- 1.1K Product Ideas
- 1.1K Ideas Exchange
- 1.2K Connect
- 966 Connectors
- 254 Workbench
- Cloud Amplifier
- 1 Federated
- 2.4K Transform
- 75 SQL DataFlows
- 499 Datasets
- 1.8K Magic ETL
- 2.7K Visualize
- 2.2K Charting
- 360 Beast Mode
- 19 Variables
- 481 Automate
- 101 Apps
- 376 APIs & Domo Developer
- 6 Workflows
- 22 Predict
- 6 Jupyter Workspaces
- 16 R & Python Tiles
- 316 Distribute
- 64 Domo Everywhere
- 252 Scheduled Reports
- 59 Manage
- 59 Governance & Security
- 1 Product Release Questions
- 5K Community Forums
- 37 Getting Started
- 23 Community Member Introductions
- 63 Community Announcements
- 4.8K Archive