Home Forums SQL Server 2008 SQL Server Newbies Creating a Ref Number usning letters and numbers. RE: Creating a Ref Number usning letters and numbers.

  • February 20, 2013 at 6:18 am

    #1589528

    wafw1971 (2/20/2013)

    I have a column on my database called Booking Number this is to be used on correspondence etc however I don't want it to be Booking Number to be just a 1, I want it to be B000000001. How can I accomplish this?

    I have tried but it has not worked.

    USE Occupancy

    Update Bookings

    Set BookingNumber = 'B' + Right ('00000000' + CAST (BookingNumber AS varchar (30)), 8)

    WHERE BookingNumber = '0'

    Thanks

    Wayne

    Always run the corresponding SELECT first. It will show you which rows will be updated, and from what to what. The corresponding SELECT for this UPDATE is this:

    SELECT

    BookingNumber,

    NewBookingNumber = 'B' + RIGHT ('00000000' + CAST (BookingNumber AS varchar (30)), 8)

    FROM Bookings

    WHERE BookingNumber = '0'

    So - which rows will be updated? If the rows displayed don't match your requirement, then change the WHERE clause

    Is the new value correct? If not, then change the expression. You're casting BookingNumber to VARCHAR(30); is it really a 30 digit number? Will the rightmost 8 digits of a 30-digit number be meaningful?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden