Convert string to a number using format

Neethin Nandakumar 0
Viewed 28

I am trying to convert a string to a number in a snowflake table using a column for the string format.

String Format Expected
30.00 ##.## 30.00
$30.12 $##.## 30.12
71.5% ##.##% 71.5
52,165 ##,###.## 52165.0

So far I have tried try_to_number(String,replace(Format,'#','9')) but this only works for $ sign. The percentage sign is still left out. We are using dbt in our system so any method to process this using dbt is also welcome but snowflake is preferred.

dbt snowflake-cloud-data-platform
1 Answers

I don't know if you really need to use the format column, Actually, the one you use doesn't return 30.00, 30.12 or 52165.0. If all you need is to extract the values you can use a simple regexp:

select string, format, regexp_replace( string, '[^0-9.]*' ) as result from test;

+--------+--------+
| STRING | RESULT |
+--------+--------+
| 30.00  |  30.00 |
| $30.12 |  30.12 |
| 71.5%  |   71.5 |
| 52,165 |  52165 |
+--------+--------+
Gokhan Atil0
Related