/*
Purpose: 將數字(全形或半行)從字串中篩選出來。
Using DB2 functions: HEX、ASCII、SUBSTRING、CHAR_LENGTH
And Recursive SQL
*/
/*
利用 Recursive SQL 的特性將字元一個一個拆開
再運用 HEX 及 ASCII 判斷字元是否為數字，是則取出，否則轉空白
同樣利用 Recursive SQL 的特性將取出的數字 pipe 起來
*/
WITH N(X,TXT,T1,LENS) AS
(
SELECT 1 X,TXT,
CASE WHEN HEX(SUBSTRING(TXT,1,1,CODEUNITS32)) BETWEEN 'EFBC90'
AND 'EFBC99' THEN
RIGHT(HEX(SUBSTRING(TXT,1,1,CODEUNITS32)),1)
WHEN ASCII(SUBSTRING(TXT,1,1,CODEUNITS32)) BETWEEN 48
AND 57 THEN
SUBSTRING(TXT,1,1,CODEUNITS32)
ELSE '' END T1,CHAR_LENGTH(TXT,CODEUNITS32) LENS
FROM (SELECT '文０９７字０１０１－123' TXT FROM SYSIBM.SYSDUMMY1) T
UNION ALL
SELECT N.X + 1,TXT,T1 ||
CASE WHEN HEX(SUBSTRING(TXT,N.X + 1,1,CODEUNITS32)) BETWEEN 'EFBC90'
AND 'EFBC99' THEN
RIGHT(HEX(SUBSTRING(TXT,N.X + 1,1,CODEUNITS32)),1)
WHEN ASCII(SUBSTRING(TXT,N.X + 1,1,CODEUNITS32)) BETWEEN 48
AND 57 THEN
SUBSTRING(TXT,N.X + 1,1,CODEUNITS32)
ELSE '' END T1,N.LENS
FROM N
WHERE N.X + 1 <= CHAR_LENGTH(TXT,CODEUNITS32)
)
SELECT * FROM N
ORDER BY TXT,X
/* 選取結果如下 */
※ 如果只要選取結果，則在加上 where 條件，Recursive 的次數 = 字串長度即可
/* 建立測試 Table，測試多筆資料 */
CREATE TABLE ORION_TEXT
(
TXT VARCHAR(60)
) IN USERSPACE1
/* 建立測試資料 */
INSERT INTO ORION_TEXT VALUES ('文０９７字號３８０９２ ７')
INSERT INTO ORION_TEXT VALUES ('作廢文０９6字號0083216')
INSERT INTO ORION_TEXT VALUES ('作廢文０９６字號0083216')
INSERT INTO ORION_TEXT VALUES ('')
INSERT INTO ORION_TEXT VALUES ('NO9701－０913８８')
/*
將使用 dummy table 的地方改為 ORION_TEXT
加入選取條件為 X = LENS
*/
WITH N(X,TXT,T1,LENS) AS
(
SELECT 1 X,TXT,
CASE WHEN HEX(SUBSTRING(TXT,1,1,CODEUNITS32)) BETWEEN 'EFBC90'
AND 'EFBC99' THEN
RIGHT(HEX(SUBSTRING(TXT,1,1,CODEUNITS32)),1)
WHEN ASCII(SUBSTRING(TXT,1,1,CODEUNITS32)) BETWEEN 48
AND 57 THEN
SUBSTRING(TXT,1,1,CODEUNITS32)
ELSE '' END T1,CHAR_LENGTH(TXT,CODEUNITS32) LENS
FROM ORION_TEXT
UNION ALL
SELECT N.X + 1,TXT,T1 ||
CASE WHEN HEX(SUBSTRING(TXT,N.X + 1,1,CODEUNITS32)) BETWEEN 'EFBC90'
AND 'EFBC99' THEN
RIGHT(HEX(SUBSTRING(TXT,N.X + 1,1,CODEUNITS32)),1)
WHEN ASCII(SUBSTRING(TXT,N.X + 1,1,CODEUNITS32)) BETWEEN 48
AND 57 THEN
SUBSTRING(TXT,N.X + 1,1,CODEUNITS32)
ELSE '' END T1,N.LENS
FROM N
WHERE N.X + 1 <= CHAR_LENGTH(TXT,CODEUNITS32)
)
SELECT TXT,T1 FROM N
WHERE X = LENS
ORDER BY TXT,X
/* 結果如下圖 */
