CODEWITHSUNDEEP
snowflake-cloud-data-platformdbt
Neethin Nandakumar
Neethin Nandakumar

Reputation: 49

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.

Upvotes: 0

Views: 1158

Answers (1)

Gokhan Atil
Gokhan Atil

Reputation: 10039

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

Upvotes: 1

Related Questions