Search code examples
snowflake-cloud-data-platformdbt

Convert string to a number using format

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.

Solution

  • 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 |
+--------+--------+