Home » SQL & PL/SQL » SQL & PL/SQL » replace multiple strings (10.1.0)
replace multiple strings [message #355396] Thu, 23 October 2008 23:05 Go to next message
maheshmhs
Messages: 93
Registered: October 2008
Location: Nepal
Member
hi,
i have a field that contains following data.(01,02,03,1,2,3)
now i have to replace 1 with 01, 2 with 02 and 3 with 03.
But 01 shouldn't become 001. Same is the case with 02 and 03.

any suggestions?

thanx.
Re: replace multiple strings [message #355411 is a reply to message #355396] Thu, 23 October 2008 23:45 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Steps to follow:
- split the string into the bits using the comma as separator (use INSTR and SUBSTR)
- check if the length of a bit < 2
- if so, lpad with '0'
- concatenate the bits

Show us that you are trying. Show (pieces of) your code where you try to execute each bit.
We will help you combine them.
Re: replace multiple strings [message #355422 is a reply to message #355396] Fri, 24 October 2008 00:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select regexp_replace('01,02,03,1,2,3',',([1-9])',',0\1') from dual;
REGEXP_REPLACE('0
-----------------
01,02,03,01,02,03

1 row selected.

Regards
Michel
Re: replace multiple strings [message #355428 is a reply to message #355422] Fri, 24 October 2008 00:55 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Read the forum guide. It has clear instructions NOT to give complete solutions.
This is the n-th time you do this, so either you stop blaming other people for doing it, or you stop giving out solutions.
Re: replace multiple strings [message #355432 is a reply to message #355428] Fri, 24 October 2008 01:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You shoud have noticed that I NEVER complained when someone posted advanced solutions containing new or advanced features like regexp functions, Barbara, Kevin, joy_division, Littlefoot, JRowbottom... complex solutions.

Regards
Michel
Re: replace multiple strings [message #355435 is a reply to message #355432] Fri, 24 October 2008 01:22 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Not entirely true: You never complain about some PEOPLE providing solutions. Has nothing to do with complexity of the given solution.
Re: replace multiple strings [message #355447 is a reply to message #355435] Fri, 24 October 2008 02:02 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is your interpretation... and it is wrong... but you are free to not trust me.

Regards
Michel
Previous Topic: oracle restore point
Next Topic: function returns multiple values (2columns, numerous rows)
Goto Forum:
  


Current Time: Sat Dec 10 14:48:19 CST 2016

Total time taken to generate the page: 0.04922 seconds