August 18, 2009

String Tokenizer

Query to tokenize comma seperated string:


SELECT TRIM(SUBSTR( txt
,INSTR(txt, ',', 1, level ) + 1
,INSTR(txt, ',', 1, level+1) - INSTR(txt, ',', 1, level) - 1
)
) AS token
FROM (SELECT ',' 'Comma,Seperated,String' ',' AS txt FROM DUAL )
CONNECT BY level <= LENGTH(txt) - LENGTH(REPLACE(txt,',','')) - 1;


Output:

TOKEN
------
Comma
Seperated
String

No comments:

Post a Comment