Link to home
Start Free TrialLog in
Avatar of dcadler
dcadler

asked on

MS SQL Query to change a table field value if it has a specific current value

I have a MS SQL 2008 database called ACIMain. In that database I have a table called PieceDetails. In the PieceDetails table is a varchar field called ZIP9. This table has several million records where 30 thousand or so of records have the ZIP9 value that has 9 digits and a dash. The 9 digits vary from record to record but they all have a dash as the 10th character. I need to select the records that have 9 digits and a dash from all of the records in the table and then remove the dash from each of the selected record.

How can I do that with a T-SQL query?


Thanks,

Dave
ASKER CERTIFIED SOLUTION
Avatar of David Todd
David Todd
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I think this query will do the trick:
update PieceDetails
set ZIP9 = STUFF( ZIP9, 10, 1, '' )
where substring( ZIP9, 10, 1 ) = '-'

Open in new window

update piecedetails
set zip9 = replace(zip9,'-','')
where right(zip9,1) = '-';
@Awking00 - that's almost exactly what I posted several comments above.
Avatar of dcadler
dcadler

ASKER

Thanks David. Thad did the trick
David Todd, I apologize. I missed reading your earlier post.
@Awking00 - apology accepted.