Snowflake - split a string based on number/letters-CodePudding

For column A, the example values are

SRAB-123456-CRTCA-700042125
TRAB-B03456-CBAC-12342125

I want to split these values so they will become

SRAB-123456
CRTCA-700042125
TRAB-B03456
CBAC-12342125

The pattern is tricky: the length of each part (SRAB, 123456, CRTCA, etc.) is random. Only one pattern is fixed: left part (SRAB,CRTCA,TRAB,CBAC,etc.) is a string only contains letters. But the right part could be letters/number.

CodePudding user response：

It could be done with

EDIT:

SELECT tab.id, tab.col, LISTAGG(s.VALUE, '-') WITHIN GROUP(ORDER BY s.index)
FROM tab
,LATERAL split_to_table(REPLACE(tab.col, '_', '-'), '-') s 
GROUP BY tab.id, tab.col, SEQ, CEIL(INDEX/2);

Output:

EDIT 2:

the table doesnt have ID column to show the order of each row,

The code still works!

CREATE OR REPLACE TABLE tab
AS
SELECT  'SRAB-123456-CRTCA-700042125' col
UNION 
SELECT 'TRAB-B03456_CBAC-12342125';


SELECT  tab.col, LISTAGG(s.VALUE, '-') WITHIN GROUP(ORDER BY s.index) AS result
FROM tab
,LATERAL split_to_table(REPLACE(tab.col, '_', '-'), '-') s 
GROUP BY  tab.col, SEQ, CEIL(INDEX/2);

Output:

CodePudding user response：

We could use SPLIT_PART here:

SELECT val
FROM
( 
    SELECT id, SPLIT_PART(a, '-', 1) || '-' || SPLIT_PART(a, '-', 2) AS val, 1 AS ord
    UNION ALL
    SELECT id, SPLIT_PART(a, '-', 3) || '-' || SPLIT_PART(a, '-', 4), 2
) t
ORDER BY id, ord;

I assume here that there exist some column id which is providing the ordering in your sample data as shown. If not, then it may not be possible to generate the output you want in that order.