Solved

SQL REPLACE Statement

Posted on 2008-10-07
5
400 Views
Last Modified: 2011-10-03
Hi,

I am trying to update a Column within a Table:

all records in column are in following format:

99-xxx-xxxx
89-xxx-xxxx
99-xxx-xxxx

I just want to update first two char '99' to '89' and rest of '-xxx-xxxx' remain intact.

Thank you in advance.

CN
0
Comment
Question by:CyberNerd
  • 4
5 Comments
 
LVL 38

Expert Comment

by:Jim P.
ID: 22664306
Update MyTable
SET MyCol = '99' + SUBSTR(MyCol, 3, 15)
WHERE Left(MyCol,2) = '89'
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 22664315
Bqckwards
Update MyTable

SET MyCol = '89' + SUBSTR(MyCol, 3, 15)

WHERE Left(MyCol,2) = '99'

Open in new window

0
 
LVL 2

Author Comment

by:CyberNerd
ID: 22664393
I've ran the update statement but I get the following error:

Msg 195, Level 15, State 10, Line 2
'SUBSTR' is not a recognized built-in function name.

Is this mean SUBSTR is not enabled in my SQL 2005 server?
0
 
LVL 38

Accepted Solution

by:
Jim P. earned 125 total points
ID: 22664514
Try substring. SUBSTR is an accepted synonym in Oracle. My bad.
Update MyTable

SET MyCol = '89' + substring(MyCol, 3, 15)

WHERE Left(MyCol,2) = '99'

Open in new window

0
 
LVL 38

Expert Comment

by:Jim P.
ID: 22664870
Glad to be of assistance. May all your days get brighter and brighter.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now