Home » SQL & PL/SQL » SQL & PL/SQL » SQL Help Needed: Split & Replace - Update Column Value
SQL Help Needed: Split & Replace - Update Column Value [message #228549] Tue, 03 April 2007 06:11 Go to next message
draculla
Messages: 6
Registered: April 2007
Junior Member
Hello experts,

I would like to seek for your expertise on how to update part of the column's value for many rows.

Let say,
I have a table (table1) with 2 columns (col1 & col2)

col1     col2
100      abc.123
200      abc.456
300      abc.789

and i need to update abc to efg for col2 to become like

col1     col2
100      efg.123
200      efg.456
300      efg.789


any clue on how should the query looks like ?

pls advise, many thanks.

[Updated on: Tue, 03 April 2007 06:17] by Moderator

Report message to a moderator

Re: SQL Help Needed: Split & Replace - Update Column Value [message #228557 is a reply to message #228549] Tue, 03 April 2007 06:20 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Check into the REPLACE function.
Re: SQL Help Needed: Split & Replace - Update Column Value [message #228558 is a reply to message #228549] Tue, 03 April 2007 06:20 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Do you want to replace abc with efg (this would mean a simple replace()) or do you want the part before the dot replaced by another literal?
SQL> with my_data as
  2  ( select 100       as col1
  3    ,      'abc.123' as col2
  4    from   dual
  5    union all
  6    select 200
  7    ,      'abc.456'
  8    from   dual
  9    union all
 10    select 300
 11    ,      'abc.789'
 12    from   dual
 13  )
 14  select col1
 15  ,      'efg'||substr(col2, instr(col2, '.'))
 16  from   my_data;

      COL1 'EFG'||SUB
---------- ----------
       100 efg.123
       200 efg.456
       300 efg.789
Re: SQL Help Needed: Split & Replace - Update Column Value [message #228559 is a reply to message #228549] Tue, 03 April 2007 06:21 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Edit: Too sloooowww again.

MHE

[Updated on: Tue, 03 April 2007 06:21]

Report message to a moderator

Re: SQL Help Needed: Split & Replace - Update Column Value [message #228592 is a reply to message #228558] Tue, 03 April 2007 07:49 Go to previous messageGo to next message
draculla
Messages: 6
Registered: April 2007
Junior Member
"want the part before the dot replaced by another literal"

that is just an example, i have a lot of rows to be updated.

thanks.
Re: SQL Help Needed: Split & Replace - Update Column Value [message #228704 is a reply to message #228592] Tue, 03 April 2007 16:14 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It is not about the number of records, but the way it should be done.

Do ALL records have the same string in front of the dot ('abc' in your example; could be anything, for example 'BBC.some_numbers', 'Uma Thurman.some_numbers', etc.)?

Or, does one record look like 'BBC.12412', another one 'Uma Thurman.241', yet another one 'Bigfoot.0093' etc.?

So, what's your case? First one or the second one?
Re: SQL Help Needed: Split & Replace - Update Column Value [message #228708 is a reply to message #228704] Tue, 03 April 2007 19:05 Go to previous messageGo to next message
draculla
Messages: 6
Registered: April 2007
Junior Member
Hi,

2nd case.
value for col2 could be anything.
however, i thought i can filter to update those started/contained abc. to efg. in the WHERE clause.

do advise, thanks.
Re: SQL Help Needed: Split & Replace - Update Column Value [message #228733 is a reply to message #228708] Wed, 04 April 2007 00:27 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
It sounds to me that either replace() or my solution would work.
Could you tell what is wrong with either one? Show us what you got, what results you get and what results you need please.
Re: SQL Help Needed: Split & Replace - Update Column Value [message #228833 is a reply to message #228733] Wed, 04 April 2007 06:31 Go to previous message
draculla
Messages: 6
Registered: April 2007
Junior Member
Hi Frank,

I've got my result with

UPDATE table1 SET col2 = REPLACE (col2, 'abc', 'efg')

thanks for the help.
Previous Topic: What is problem
Next Topic: union keyword
Goto Forum:
  


Current Time: Fri Dec 02 13:47:34 CST 2016

Total time taken to generate the page: 0.12979 seconds