Filtering strings that contain letters

Chris_Kent · 2024-04-19T03:55:17+00:00

There was an error rendering this rich post.

Chris_Kent

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 serialLIKE ('%[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…

Find more posts tagged with

Filters

Magic ETL

Formulas

Accepted answers

GrantSmith

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]

GrantSmith

^ - 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

All comments

Chris_Kent

This seems to work.

CASE
WHEN SERIAL~* ([a-z]) THEN 0
ELSE 1
END

GrantSmith

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]

Chris_Kent

Hi Grant,

Thanks, that works well.

Do you know where I can find information on where symbols like ^ and $ are explained?

Cheers,

Chris

GrantSmith

^ - 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

Chris_Kent

Thanks Grant, much appreciated.