CODEWITHSUNDEEP
snowflake-cloud-data-platformsnowflake-schema
BMX_01
BMX_01

Reputation: 45

snowflake number convertion based on digits and find the list of numbers between the range

Trying to standardize column b numbers and find the numbers between them

ColumnA ColumnB
20 34
113 17
308 12

need to change column b based on the column a and list of numbers between them.

ColumnA ColumnB ColumnBupdated numberlist
20 34 34 [20,21,22,23,24,25,26,27,28,29,30,31,32,33,34]
113 17 117 [113,114,115,116,117]
308 12 312 [308,309,310,311,312] 
select (case when length(column_A) <> length(column_b) and column_b is not null and column_b <>'' and to_number(column_A % 2) = 0  then TO_NUMBER(column_A) + TO_NUMBER(column_b) when length(column_A) <> length(column_b) and column_b is not null and column_b <>'' and to_number(column_A % 2) = 1  then TO_NUMBER(column_A) + TO_NUMBER(column_b) -1 else column_b end) as column_b_Clean from table;

Upvotes: 1

Views: 95

Answers (1)

Adrian White
Adrian White

Reputation: 1804

enter image description here

So using the LPAD() we can resolve the first problem of getting ColumnBUpdated sorted.

Next we can use REPEAT() to get a nice list of numbers between ColumnA and the ColumnBUpdated.

Then we just slap it into an array using ARRAY_AGG() using the INDEX variable that comes free from our Lateral Flatten() and Bob's your uncle!

with cte as( 
       select 20 ColumnA, 34 ColumnB
union all select 113 ColumnA, 17 ColumnB
union all select 308 ColumnA, 12 ColumnB) 
, cte2 as (select ColumnA,ColumnB,LPAD(ColumnB,length(ColumnA),ColumnA) ColumnBUpdated,ColumnBUpdated-ColumnA Q  from cte) 

SELECT 
    ColumnA
,   ColumnB
,   ColumnBUpdated
,   ARRAY_AGG(COLUMNA+INDEX)   
FROM 
    cte2 
    , LATERAL SPLIT_TO_TABLE(REPEAT('hire me $3/hour',Q),'hire me $3/hour') 
GROUP BY 
    1,2,3 ;

An alternative solution:

SELECT 
    ColumnA
,   ColumnB
,   ColumnBUpdated
,   ARRAY_AGG(COLUMNA+INDEX)   
FROM  
     CTE2 
    ,LATERAL FLATTEN(INPUT=>STRTOK_TO_ARRAY(REPEAT('1~',Q),'~') ) 
GROUP BY 1,2,3

Upvotes: 1

Related Questions