/*
Purpose: eliminate none digital data from strings
Using functions: TO_SINGLE_BYTE、ASCII、SYS_CONNECT_BY_PATH、OLAP Function
And Recursive SQL: Connect by
*/
/* Marvelous Function: TO_SINGLE_BYTE */
SELECT TO_SINGLE_BYTE('０９７年０１０１＃NO.００１') FROM DUAL
/* Result is */
097年0101#NO.001
/* Chinese characters also turn out to be single-byte */
SELECT SUBSTR(TO_SINGLE_BYTE('０９７年０１０１＃NO.００１'),4,1) FROM DUAL
/* Result is */
年
/*
Using Recursive SQL to separate each character
Choose number whichever ascii code between 48 and 59
Columns (ONO、PRECURSOR) made this inner view a self join-like table
Column LENS made of Olap function which counting numbers
*/
SELECT ROWNUM ONO,DATA,ROWNUM - 1 PRECURSOR,COUNT(*) OVER() LENS
FROM (SELECT TXT,
(CASE WHEN ASCII(SUBSTR(TXT,LEVEL,1)) BETWEEN 48 AND 59 THEN
SUBSTR(TXT,LEVEL,1) END) DATA
FROM (SELECT TO_SINGLE_BYTE('０９７＃０１－ＮＯ－２９００１３９７６') TXT
FROM DUAL) T
CONNECT BY LEVEL <= LENGTH(TXT)
) T
WHERE DATA IS NOT NULL
/* Result is */
/*
use inner view above to make up numbers
SYS_CONNECT_BY_PATH keeps each recursive result of data
*/
SELECT TXT,REPLACE(SYS_CONNECT_BY_PATH(DATA,' '),' ','') HIST
FROM (SELECT TXT,ROWNUM ONO,DATA,ROWNUM - 1 PRECURSOR,
COUNT(*) OVER() LENS
FROM (SELECT TXT,
(CASE WHEN ASCII(SUBSTR(TXT,LEVEL,1)) BETWEEN 48 AND 59
THEN SUBSTR(TXT,LEVEL,1) END) DATA
FROM (SELECT TO_SINGLE_BYTE('０９７＃０１－ＮＯ－２９００１３９７６') TXT
FROM DUAL) T
CONNECT BY LEVEL <= LENGTH(TXT)
) T WHERE DATA IS NOT NULL
)T2
WHERE ONO = LENS
START WITH ONO = 1
CONNECT BY PRIOR ONO = PRECURSOR
/* Result is */
