CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Zoom
Zoom

Reputation: 75

Snowflake if String Pattern replace

I have column which should only contain numbers[0-9], But in some cases we started see alphanumeric[eg:p8eSadfghrc] values in that column. I wanna write a condition where if the value is not completely numeric{0-9}, i wanna replace it with another value from another column.

Upvotes: 0

Views: 1180

Answers (2)

GMB
GMB

Reputation: 222462

In Snowflake, I would recommend try_to_decimal(). It attempts to convert the number to a decimal value (you control the target precision and scale with arguments) and rturns null if that fails:

select t.*, case when try_to_decimal(mycol) is null then myothercol else mycol end newcol
from mytable

If you want an update statement:

update mytable
set mycol = myothercol
where try_to_decimal(mycol) is null

When given no second and third argument, the number of allowed digits is 38, with 0 decimals (which seems to be what you want).

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Something like this?

update t
    set col = <other value>
    where regexp_like(col, '[^0-9]');

This updates the data. You could also just do this in a query:

select t.*,
       (case when regexp_like(col, '[^0-9]') then <other value> else col end)
from t;

Upvotes: 1

Related Questions