[20260228]如何实现字符串拆分输出数字序列.txt

[20260228]如何实现字符串拆分输出数字序列.txt --//工作需要，需要实现输入字符串拆分输出数字序列。比如： --//输入 1-3,5,7-10,44 --//输出 1,2,3,5,7,8,9,10,44 --//想了半天感觉不好写，上kimi查询，自己改了一改，修改如下： $ cat numlist2.sh #! /bin/bash echo "$1" | awk '{n=split($0,a,","); for(i=1;i<=n;i++){if(a[i]~/-/){split(a[i],b,"-"); for(j=b[1];j<=b[2];j++)printf j","}else printf a[i]","}}' | sed 's/,$/\n/' --//该版本比较复杂。 --//其中给出一个版本测试存在问题，seq ${r/-/ }后面的参数被当作一个参数，执行报错： --//bash numlist() { local IFS=,; for r in $1; do [[ $r =~ - ]] && seq ${r/-/ } | tr '\n' , || echo -n "$r,"; done | sed 's/,$//'; } numlist "2-5,7,10" --//不过很好更正，改写如下： $ cat ./numlist.sh #! /bin/bash IFS=, for r in $1 do      [[ $r =~ - ]] &&  eval seq ${r/-/ } || echo  "$r" done  | paste -sd, --//AI给出的解决方案，感觉没我改写的简单明了！ $ ./numlist.sh "1- 3, 5, 7-10,44" 1,2,3, 5,7,8,9,10,44 --//输入存在空格单个数字会原样输出,这个问题不是很大。在循环体内修改IFS定义,修改echo $r就可以了：  $ cat numlist.sh #! /bin/bash IFS=, for r in ${1} do      IFS=' \t\n'      [[ $r =~ - ]] &&  eval seq ${r/-/ } || echo $r done  | paste -sd, $ ~/sqllaji/bin/numlist.sh " 1 - 3, 5 ,      7 -10,44" 1,2,3,5,7,8,9,10,44 --//写成一行的脚本如下： $ cat numlist.sh #! /bin/bash IFS=, ; for r in $1  ; do IFS=' \t\n';[[ $r =~ - ]] &&  eval seq ${r/-/ } || echo  $r; done  | paste -sd, --//缺点无法删除重复值，不过这个很容易修改，加入sort -n，uniq过滤管道就可以了。最终修改如下： $ cat numlist.sh #! /bin/bash IFS=, for r in ${1} do      IFS=' \t\n'      [[ $r =~ - ]] &&  eval seq ${r/-/ } || echo $r done  | sort -n | uniq |paste -sd, --//写sql语句呢？ --//sql WITH input_data AS (     -- 输入字符串     SELECT '&1' AS input_str FROM DUAL ), split_parts AS (     -- 步骤1：按逗号拆分     SELECT         REGEXP_SUBSTR(input_str, '[^,]+', 1, LEVEL) AS part     FROM input_data     CONNECT BY REGEXP_SUBSTR(input_str, '[^,]+', 1, LEVEL) IS NOT NULL ), expanded AS (     -- 步骤2：处理每个部分(范围或单个数字)     SELECT         CASE             -- 如果是范围格式(如 2-5)             WHEN INSTR(part, '-') > 0 THEN                 TO_NUMBER(REGEXP_SUBSTR(part, '^[0-9]+'))             -- 如果是单个数字             ELSE TO_NUMBER(part)         END AS start_num,         CASE             WHEN INSTR(part, '-') > 0 THEN                 TO_NUMBER(REGEXP_SUBSTR(part, '[0-9]+$'))             ELSE TO_NUMBER(part)         END AS end_num     FROM split_parts ), -- 步骤3：使用递归生成序列 numbers(n, end_n) AS (     SELECT start_num, end_num FROM expanded     UNION ALL     SELECT n + 1, end_n     FROM numbers     WHERE n < end_n ) -- 最终结果 SELECT -- distinct n AS result_number LISTAGG (distinct n, ',') WITHIN GROUP (ORDER BY n) num_lists     FROM numbers ORDER BY n; --// Oracle 10g 兼容版本(非递归) WITH input_data AS (SELECT '2-5,7,10' AS input_str FROM DUAL), split_parts AS (     SELECT REGEXP_SUBSTR(input_str, '[^,]+', 1, LEVEL) AS part     FROM input_data     CONNECT BY REGEXP_SUBSTR(input_str, '[^,]+', 1, LEVEL) IS NOT NULL ), expanded AS (     SELECT         TO_NUMBER(REGEXP_SUBSTR(part, '^[0-9]+')) AS start_num,         CASE             WHEN INSTR(part, '-') > 0             THEN TO_NUMBER(REGEXP_SUBSTR(part, '[0-9]+$'))             ELSE TO_NUMBER(REGEXP_SUBSTR(part, '^[0-9]+'))         END AS end_num     FROM split_parts ) SELECT start_num + LEVEL - 1 AS result_number FROM expanded CONNECT BY LEVEL <= end_num - start_num + 1 AND PRIOR start_num = start_num  -- 防止笛卡尔积 AND PRIOR SYS_GUID() IS NOT NULL ORDER BY result_number; --//第2个写法使用CONNECT BY，连接的条件非常特别PRIOR start_num = start_num，后面好跟着PRIOR SYS_GUID() IS NOT NULL。 --//而且这个版本字符串中间不能存在空格并且没有去除重复值，修改如下： $ cat ff1.sql WITH input_data AS (SELECT '&&1' AS input_str FROM DUAL), split_parts AS (     SELECT REGEXP_SUBSTR(input_str, '[^,]+', 1, LEVEL) AS part     FROM input_data     CONNECT BY REGEXP_SUBSTR(input_str, '[^,]+', 1, LEVEL) IS NOT NULL ), expanded AS (     SELECT         rownum rn,         TO_NUMBER(REGEXP_SUBSTR(part, '[0-9]+')) AS start_num,         CASE             WHEN INSTR(part, '-') > 0             THEN TO_NUMBER(REGEXP_SUBSTR(part, '[0-9]+$'))             ELSE TO_NUMBER(REGEXP_SUBSTR(part, '[0-9]+'))         END AS end_num     FROM split_parts ) --select * from expanded SELECT distinct start_num + LEVEL - 1 AS result_number FROM expanded CONNECT BY LEVEL <= end_num - start_num + 1 AND PRIOR rn = rn  -- 防止笛卡尔积 AND PRIOR SYS_GUID() IS NOT NULL --AND PRIOR dbms_random.random IS NOT NULL ORDER BY result_number / SCOTT@book01p> @ ff1.sql "1-4, 5 ,7 ,9-11,1-4" RESULT_NUMBER -------------             1             2             3             4             5             7             9            10            11 9 rows selected.