CODEWITHSUNDEEP
sqlsql-servernumeric
Rockshah
Rockshah

Reputation: 77

SQL / Celonis - where field only contains just numeric, remove anything else

In a field I want to keep those where it is only a number between 100000 and 999999

This field has a mixture of numeric, alphanumeric, alphabet characters.

This function will keep if there is a number in a field e.g. keep abc123 where I would want that removed

where some_column NOT LIKE '%[^0-9]%'

Additional - Celonis Equivalent ?

Gordon has kindly provided SQL Server coding to do this exactly as intended.

I was hoping this would work in Celonis but the try_convert function is not supported. Is there another method?

where try_convert(int, some_column) between 100000 and 999999

Upvotes: 1

Views: 429

Answers (2)

JPS
JPS

Reputation: 57

First of all it is good to know that Celonis uses Vertica SQL for its event collection. Vertica has some specific functions, that you can find at https://www.vertica.com/docs/.

That said, I'm not sure if I understand your question well, but I think that the REGEXP_ILIKE is something you could use, where '[0-9]' could be the expression you are looking for to check if your field contains a numeric value.

If you want to extract the number itself (for instance to check if it is between 100000 and 999999, use then REGEXP_SUBSTR.

Your code will be something like: WHERE REGEXP_SUBSTR(some_column, [0-9]) BETWEEN 100000 AND 999999

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Your syntax suggests you are using SQL Server. If so, try:

where try_convert(int, some_column) between 100000 and 999999

Upvotes: 1

Related Questions