OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » sql question

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

sql question [message #11186]

Wed, 10 March 2004 16:06

macintosh
Messages: 1
Registered: March 2004

Junior Member

I got a question. I need to change the first 3 digit of phone number in the database using UPDATE statement. A 929 area code should be changed into 920. I know that I should use SUBSTR to find the first part of the 929 phone number. But I am confused how to use SUBSTR in order to concatenate 920 back onto the remainder of the number.

Below is the code that I create so far, though it still resulted in error. The examples of phone number format are: 929-763-1283, 929-498-7144.

UPDATE Table

SET Phone = '920||||Substr(Phone, 4, 9)'

WHERE Substr(Phone, 1, 3) = '929';

UPDATE Table

�� *

ERROR at line 1:

ORA-01401: inserted value too large for column

Thanks.

�

Report message to a moderator

Re: sql question [message #11188 is a reply to message #11186]

Wed, 10 March 2004 17:56

sverch
Messages: 582
Registered: December 2000

Senior Member

I cannot verify it right now, but you can try something like this
UPDATE TABLE SET phone=REPLACE(SUBSTR(PHONE,1,3),'929','920');

Report message to a moderator

Re: sql question [message #11189 is a reply to message #11188]

Wed, 10 March 2004 18:06

sverch
Messages: 582
Registered: December 2000

Senior Member

And then you cancatinate the rest of the phone

Report message to a moderator

Re: sql question [message #11191 is a reply to message #11186]

Wed, 10 March 2004 23:05

William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK

Senior Member

UPDATE table
SET    phone = '920' || SUBSTR(phone,4)
WHERE  phone LIKE '929%'

LIKE allows use of an index if one exists.

Report message to a moderator

Re: sql question [message #11242 is a reply to message #11186]

Mon, 15 March 2004 10:35

Larry Lufkin
Messages: 44
Registered: March 2004

Member

SET Phone = '920' || Substr(Phone, 4)

WHERE Substr(Phone, 1, 3) = '929';

Report message to a moderator

Previous Topic:	How to Batch Insert ?
Next Topic:	Remove Primary key in this situation?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 20:22:15 CDT 2024