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

param,value1,str1,param,value2,str3,param,value3

and i want to replace the value after the param with a CONSTANT

so, the string would become

param,CONSTANT,str1,param,CONSTANT,str3,param,CONSTANT

thanks in advance.

Answers


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.


Need Your Help

More efficient way to fetch data from MySQL

php mysql sql mysqli

Suppose I have a MySQL table called "User".

Blur an image of specific part (rectangular, circular)?

iphone objective-c ios

I want to blur image rectangular or circular. After googling, I found that it is easy to blur whole image but it difficult to blur specific part of image (rectangular, circular). so how it is possi...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.