Separate integers from an string expression in snowflake-CodePudding

Hi I wanted to separate integers in a string an build an array For example 50 80 in column I wanted to get [50,80] this is simple example in my column I will have more complex expressions such as (10 20) / 30 I want [10,20,30]
I may have ,-,*,/,(,),' ' remove all this and wanted to build and array with comma separated numbers.

I tried replace but the output is string so my array is coming as ['10,20,30'] can we do this regexp

CodePudding user response：

I will simplify your question to:

How do I transform the string '10,20,30' into an array of numbers?

A quick hack to solve this is to use parse_json():

with data as (select '10,20,30' x)

select parse_json('['|| x ||']')
from data

CodePudding user response：

I'd go with regexp_substr_all

set str='(10   20) / 30';

select regexp_substr_all($str,'[0-9] ');