Home » SQL & PL/SQL » SQL & PL/SQL » Substitue characters in field
Substitue characters in field [message #209083] Wed, 13 December 2006 04:29 Go to next message
toenie2
Messages: 7
Registered: November 2006
Junior Member
Hi,

I'm trying to change 10000+ telephone numbers in a table using an update script. Problem is that I only want to change a part of the number.

Eg: 070-3703811 should be 070-7794811

How can I achieve this?

Thx.
Re: Substitue characters in field [message #209086 is a reply to message #209083] Wed, 13 December 2006 04:40 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Use SUBSTR e.g.

update table set tel_no =
substr(tel_no, 1, 4)||<new value>||substr(tel_no,9)
Re: Substitue characters in field [message #209087 is a reply to message #209083] Wed, 13 December 2006 04:41 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
You can use the REPLACE function.
icon14.gif  Re: Substitue characters in field [message #209133 is a reply to message #209087] Wed, 13 December 2006 07:56 Go to previous message
toenie2
Messages: 7
Registered: November 2006
Junior Member
You're absolutely right! I first deleted spaces, then changed values:

UPDATE ITSM_PERSONS SET PER_PRIMARYTELNR = REPLACE(PER_PRIMARYTELNR, ' ', '') 
UPDATE ITSM_PERSONS SET PER_PRIMARYTELNR = REPLACE(PER_PRIMARYTELNR, '070-3703', '070-7794')


Thx!
Previous Topic: Loading data into Oracle from Excel
Next Topic: order by
Goto Forum:
  


Current Time: Mon Dec 05 04:38:59 CST 2016

Total time taken to generate the page: 0.13341 seconds