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.
2 Answers
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;
3 Comments
Zoom
it fails if if there one number in the value.
Darren Gardner
The expression {regexp_like(col, '[^0-9]')} is equivalent to the regex ^[^0-9]$, which is true if and only if col is a string that contains exactly one non-digit. Change it to {regexp_like(col, '.*[^0-9].*')} and you should have what you are seeking.
Gordon Linoff
@Zoom . . . The expression returns true if any character in the column is NOT a digit. Even one non-digit will return true. That is what the question seems to be asking for.
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).
