parse and replace in oracle
I am new to the SQL/Oracle, and wondering if there's an easy way to parse a csv string and replace tokens with another string.
for example, i have a string like
and i want to replace the value after the param with a CONSTANT
so, the string would become
thanks in advance.
You can try REGEXP_REPLACE
select regexp_replace(regexp_replace(regexp_replace( str,'[^,]*','CONSTANT',1,3),'[^,]*','CONSTANT',1,9),'[^,]*','CONSTANT',1,15) from (select 'param,value1,str1,param,value2,str3,param,value3' str from dual);
If you have anything messy (like quoted values including commas) then it will break. That said regular expressions aren't my strong point and some should eb able to do a better job.
I presume you know that having a RDBMS like Oracle and storing the data in such an odd manner is a pretty poor idea. If you are not going for a convetional table/column structure, you can use the object relation features or XML.