Filtering strings that contain letters
I want to filter out strings that contain letters in my ETL.
e.g.
Serial
112233
445566PN
In the ETL
Add Formula
Column Name: IsInt
CASE
WHEN serial
LIKE ('%[A-Z]%') THEN 0
ELSE 1
END
EDIT
CASE
WHEN SERIAL
~* ([a-z]) THEN 0
ELSE 1
END
This is working for A-Z and is case insensitive.
Next question, is how do I filter out other characters?
Specifically, one serial number has a % sign in it, which is the wildcard symbol…
Best Answers
-
If you're wanting to only find values which are all numbers you can use a regular expression:
CASE WHEN REGEP_LIKE(`SERIAL`, '^[0-9]+$') THEN 1 ELSE 0 END
If you're wanting to include numeric like characters as well (decimals and dashes) you can use \d instead of [0-9]
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
^ - Start of the text
$ - End of the text
Combining them together in the rexex above simple enforces that the entire string is made of numbers instead of just finding numbers anywhere in the string
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1
Answers
-
This seems to work.
CASE
WHENSERIAL
~* ([a-z]) THEN 0
ELSE 1
END0 -
If you're wanting to only find values which are all numbers you can use a regular expression:
CASE WHEN REGEP_LIKE(`SERIAL`, '^[0-9]+$') THEN 1 ELSE 0 END
If you're wanting to include numeric like characters as well (decimals and dashes) you can use \d instead of [0-9]
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
Hi Grant,
Thanks, that works well.
Do you know where I can find information on where symbols like ^ and $ are explained?
Cheers,
Chris
0 -
^ - Start of the text
$ - End of the text
Combining them together in the rexex above simple enforces that the entire string is made of numbers instead of just finding numbers anywhere in the string
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
Thanks Grant, much appreciated.
0
Categories
- All Categories
- 1.5K Product Ideas
- 1.5K Ideas Exchange
- 1.4K Connect
- 1.1K Connectors
- 278 Workbench
- 4 Cloud Amplifier
- 4 Federated
- 2.7K Transform
- 89 SQL DataFlows
- 559 Datasets
- 2K Magic ETL
- 3.3K Visualize
- 2.3K Charting
- 574 Beast Mode
- 12 App Studio
- 28 Variables
- 582 Automate
- 141 Apps
- 414 APIs & Domo Developer
- 26 Workflows
- 1 DomoAI
- 28 Predict
- 12 Jupyter Workspaces
- 16 R & Python Tiles
- 355 Distribute
- 94 Domo Everywhere
- 259 Scheduled Reports
- 2 Software Integrations
- 92 Manage
- 89 Governance & Security
- 9 Product Release Questions
- Community Forums
- 42 Getting Started
- 28 Community Member Introductions
- 89 Community Announcements
- 4.8K Archive