• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 456
  • Last Modified:

SQL REPLACE Statement

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
CyberNerd
Asked:
CyberNerd
  • 4
1 Solution
 
Jim P.Commented:
Update MyTable
SET MyCol = '99' + SUBSTR(MyCol, 3, 15)
WHERE Left(MyCol,2) = '89'
0
 
Jim P.Commented:
Bqckwards
Update MyTable
SET MyCol = '89' + SUBSTR(MyCol, 3, 15)
WHERE Left(MyCol,2) = '99'

Open in new window

0
 
CyberNerdAuthor Commented:
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
 
Jim P.Commented:
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
 
Jim P.Commented:
Glad to be of assistance. May all your days get brighter and brighter.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now