Snowflake - split a string based on number/letters

Question

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.

Answer 1

It could be done with SPLIT_TO_TABLE(the number of sections could vary):

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

Query:

Actually the separator could be - or _

SELECT *
FROM tab
,LATERAL split_to_table(REPLACE(tab.col, '_', '-'), '-') s ;

Output:

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:

Answer 2

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.