Sql 在Snowflake中，如何将以逗号和连字符分隔的多个混合数值拆分成数字列表

使用array_generate_range和split_to_table函数try 以下操作:

WITH T AS (
SELECT 
  TB.ID, 
  TB.SHIFT, 
  TB.TYPE, 
  TB.DOW_NUM,
  NULLIF(SP.VALUE, '') AS COMMA_SPLIT_VAL
FROM TBL_NAME TB, 
LATERAL SPLIT_TO_TABLE(TB.DOW_NUM, ',') SP
) 
SELECT 
  T.ID, 
  T.SHIFT, 
  T.TYPE, 
  T.DOW_NUM, 
  LISTAGG(GR.VALUE, ', ') WITHIN GROUP (ORDER BY GR.VALUE) NEW_DOW_NUM
FROM T, 
TABLE(
   FLATTEN(
           ARRAY_GENERATE_RANGE(SPLIT_PART(T.COMMA_SPLIT_VAL, '-', 1), 
           COALESCE(NULLIF(SPLIT_PART(T.COMMA_SPLIT_VAL, '-', 2), ''),
           SPLIT_PART(T.COMMA_SPLIT_VAL, '-', 1)) +1, 1),  
           OUTER => TRUE
          )
      ) GR
GROUP BY T.ID, T.SHIFT, T.TYPE, T.DOW_NUM
ORDER BY T.ID, T.SHIFT, T.TYPE;

输出示例:

ID  SHIFT   TYPE    DOW_NUM       NEW_DOW_NUM
0   101     A1      ,2-5,         2, 3, 4, 5
1   102     A8      1-3,5-6,      1, 2, 3, 5, 6
1   213     B5      1,3-4,7,      1, 3, 4, 7
2   150     Q9      ,1-3,5-6,     1, 2, 3, 5, 6
2   153     X5      1-3,5-6       1, 2, 3, 5, 6
3   567     Q3      ,1-3,5-6      1, 2, 3, 5, 6
4   345     T6      1-3,5,        1, 2, 3, 5
5   555     H6H     ,1-3,5,       1, 2, 3, 5
6   353     HU78    ,1-3,5        1, 2, 3, 5
12  839     QWRETY4 7,            7
13  466     R5       ,