Update part of a field in SQL Server

Posted on 2008-11-19
Last Modified: 2012-05-05
I have a column with fields that containt 10 digit numbers and conditions, such as 'Very Good', or 'Acceptable'. (Field is named Sku). How would I got about updating the field so that I left the 10 digit numbers intact but replaced the words ex. 'Very Good' with 'VG'. I also want to eliminate any spaces between the digits and the words so 0123456789 Very Good would become 0123456789VG. FYI, the 10 digit number always comes first in the field. Thank you.
Question by:bpfsr
    LVL 32

    Expert Comment

    by:Daniel Wilson
    update MyTable set SKU = Replace(Replace(SKU, 'Very good', 'VG'), ' VG', 'VG')

    Author Comment

    Thank you. If you don't mind, can you explain the logic just so I get a better understanding, thanks.
    LVL 59

    Expert Comment

    by:Kevin Cross
    What Daniel is showing is updating the field with same value currently in that field but using the REPLACE function to change instance(s) of 'Very good' with 'VG'.  Then in a second replace change ' VG' to 'VG'.

    So in terms of your data, it is doing this in the two steps:

    1>0123456789 Very Good --> 0123456789 VG
    2>0123456789 VG --> 0123456789VG
    LVL 59

    Expert Comment

    by:Kevin Cross
    LVL 32

    Accepted Solution

    Thanks for the followup & explanation, Kevin.
    LVL 59

    Assisted Solution

    by:Kevin Cross
    Hopefully I did your answer justice. :)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    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
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    760 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

    10 Experts available now in Live!

    Get 1:1 Help Now