Regular Expressions

Ed Edwards from Florida Department Of Education asked me if we can use the new Oracle 10g Regular Expressions to rewrite this sql he had written to determine ALL the position of ',' in a string.

select distinct text1, 'A "," is located in position ' || instr(text1, ',', 1, level) pos
from (select text1 from test2)
connect by level <= length(text1) - length(replace(text1, ','))
order by text1 desc, pos asc


I must say I really liked his clever way of repeating the rows as many times as the commas using the connectby level <= numberof commas.

Worthy of saving it as a tip.