Home » SQL & PL/SQL » SQL & PL/SQL » Replacing variable string with a fixed part
Replacing variable string with a fixed part [message #217491] Fri, 02 February 2007 09:10 Go to next message
agostino_neto
Messages: 180
Registered: July 2005
Senior Member
Hi all,

Do some of you know how to replace with a single command ?
"YYY ACBBA HHH" by "YYY TEST1 ATEST2 HHH"
"XXX ZZZCBBA JJJ" by "XXX TEST1 ZZZ.TEST2 JJJ"
Here the pattern is CBBA.
I want to replace 'CBBA' with 'TEST2'.
I want to add something before the word (or the letter) appearing before the pattern CBBA.
In this exemple, I want to add something only before 'A' and 'ZZZ'
Perhaps we need to use Pl/Sql.
Thanks.
Re: Replacing variable string with a fixed part [message #217497 is a reply to message #217491] Fri, 02 February 2007 09:34 Go to previous messageGo to next message
Buchas
Messages: 83
Registered: March 2006
Member
hi

select replace ('YYY ACBBA HHH','CBBA','TEST2') from dual
select replace ('XXX ZZZCBBA JJJ','CBBA','TEST2') from dual

should help?
Re: Replacing variable string with a fixed part [message #217500 is a reply to message #217497] Fri, 02 February 2007 09:46 Go to previous messageGo to next message
agostino_neto
Messages: 180
Registered: July 2005
Senior Member
Hi Buchas,
select replace ('YYY ACBBA HHH','CBBA','TEST2') from dual
gives
"YYY ATEST2 HHH" instead of
"YYY TEST1 ATEST2 HHH"

I am losing "TEST1 ".
Re: Replacing variable string with a fixed part [message #217511 is a reply to message #217500] Fri, 02 February 2007 10:45 Go to previous messageGo to next message
Buchas
Messages: 83
Registered: March 2006
Member
How would you define then, why do you need exactly "YYY TEST1 ATEST2 HHH" and not lets say "YY TEST1Y ATEST2 HHH" or "Y TEST1YY ATEST2 HHH" etc. ?

Regular expressions come to my mind, though I have never used them in Oracle. Maybe googling for 'oracle regular expression' you will find something interesting.
Re: Replacing variable string with a fixed part [message #217526 is a reply to message #217491] Fri, 02 February 2007 11:54 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Knowing what version you are using helps with providing a solution. If you are on 10g:

SQL> select a1, regexp_replace(a1, '(\S+) (\S+)(CBBA)','\1 TEST1 \2TEST2') NEW from test_tab;

A1                             NEW
------------------------------ ------------------------------
YYY ACBBA HHH                  YYY TEST1 ATEST2 HHH
XXX ZZZCBBA JJJ                XXX TEST1 ZZZTEST2 JJJ
Re: Replacing variable string with a fixed part [message #217565 is a reply to message #217526] Fri, 02 February 2007 16:38 Go to previous messageGo to next message
agostino_neto
Messages: 180
Registered: July 2005
Senior Member
Thanks ebrian.
That code should work for different databases.
What option do we have on 8i or 9i ? Pl/Sql only ?
Re: Replacing variable string with a fixed part [message #217568 is a reply to message #217565] Fri, 02 February 2007 17:12 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
If the data that you provided is a true representation of your data, then the following should work for 8i & 9i:

SQL> select a1,
  2     substr(a1, 1, instr(a1, ' '))||'TEST1'||substr(replace(a1, 'CBBA', 'TEST2'), instr(a1, ' ')) NEW from test_tab;

A1                             NEW
------------------------------ ------------------------------
YYY ACBBA HHH                  YYY TEST1 ATEST2 HHH
XXX ZZZCBBA JJJ                XXX TEST1 ZZZTEST2 JJJ

[Updated on: Fri, 02 February 2007 17:13]

Report message to a moderator

Re: Replacing variable string with a fixed part [message #217613 is a reply to message #217568] Sat, 03 February 2007 02:28 Go to previous messageGo to next message
agostino_neto
Messages: 180
Registered: July 2005
Senior Member
Thanks ebrian.
Unfortunately,it is not a true representation my data.
Instead of ' ', I can also have ',' or I can have no special characters (it is the case when there is a new line on my string/long word).
Also when I have ' ', it will not always be the first ' ' in that string/long word.
icon7.gif  Re: Replacing variable string with a fixed part [message #218229 is a reply to message #217491] Wed, 07 February 2007 07:02 Go to previous messageGo to next message
dewildeh
Messages: 3
Registered: February 2007
Junior Member
If your definition of a single command includes nested replace statements you can use the following:

SELECT a1
     , REPLACE(
         REPLACE(
           REPLACE( a1
           , 'ZZZCBBA', 'TEST1 ZZZTEST2' )
         , 'ACBBA', 'TEST1 ATEST2')
       , 'CBBA', 'TEST2') new
FROM   Test_Tab
/

A1                        NEW
------------------------- -------------------------
YYY ACBBA HHH             YYY TEST1 ATEST2 HHH
XXX ZZZCBBA JJ            XXX TEST1 ZZZTEST2 JJ

Care should be taken that the biggest replace strings are taken first. Otherwise the last replace ('CBBA', 'TEST2') removes part of the pattern the earlier replaces need. Smile

Re: Replacing variable string with a fixed part [message #218253 is a reply to message #218229] Wed, 07 February 2007 08:27 Go to previous message
agostino_neto
Messages: 180
Registered: July 2005
Senior Member
Thanks dewildeh.

The only problem is that your solution is not generic. It will work only for the previous examples.
I found a more appropriate solution on this site here
Previous Topic: New Database connection
Next Topic: Need quick help with a Max Query
Goto Forum:
  


Current Time: Tue Dec 06 12:33:16 CST 2016

Total time taken to generate the page: 0.07898 seconds