Problem: Given any sentence, break it into separate words. (The words are separated by spaces).
Solution: select doc.EXTRACT('/l/text()').getstringVal() from (select 'I am a bad man' given from dual ) T , TABLE(xmlSequence(EXTRACT(XMLTYPE(''REPLACE(T.given,' ','')''),'/doc/l'))) doc
This can also be achieved using SELECT TRIM(',' FROM SUBSTR('I AM A BAD MAN'||' ',NVL(LAG(R.T) OVER (ORDER BY R.T),0)+1,R.T-NVL(LAG(R.T) OVER (ORDER BY R.T),0))) DONE FROM (SELECT INSTR('I AM A BAD MAN'||' ',' ',1,ROWNUM) T FROM ALL_OBJECTS)R WHERE R.T<>0
This can also be achieved using
ReplyDeleteSELECT TRIM(',' FROM SUBSTR('I AM A BAD MAN'||' ',NVL(LAG(R.T) OVER (ORDER BY R.T),0)+1,R.T-NVL(LAG(R.T) OVER (ORDER BY R.T),0))) DONE
FROM
(SELECT INSTR('I AM A BAD MAN'||' ',' ',1,ROWNUM) T FROM ALL_OBJECTS)R
WHERE R.T<>0
Kartik