Solved

SQL REPLACE Statement

Posted on 2008-10-07
5
426 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Check ALL SP in database make sure there are no errors 17 45
SQL Activity Monitor detail 2 28
RESTORE A BACKUP IN SQL 2012 from SQL 2008 9 65
RAISERROR WITH NOWAIT 2 18
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

830 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