博客 / 詳情
返回[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.
