Creating a Ref Number usning letters and numbers.

Question

Creating a Ref Number usning letters and numbers.

wafw1971

SSC Eights!

Points: 976
More actions
February 20, 2013 at 5:56 am

#289800

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

Is booking number varchar(30) in the DB?

If not look at computed columns and stick an extra column on the end of the table with the formula given to convert it to B00000000 etc

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

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

Dwain Camps SSC Guru Points: 86948 More actions · Answer 3

ChrisM@Work (2/20/2013)
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:

+1 INSERTs too!

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

wafw1971 SSC Eights! Points: 976 More actions · Answer 4

Hi Dwain

For some reason that didn't work, but I found another solution.

USE Occupancy

Update t

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

FROM (SELECT *,ROW_NUMBER() OVER (ORDER BY Booking_Skey) AS Seq FROM Bookings)t

Thanks for your help.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Try this, Wayne:

USE Occupancy

;WITH BookingsToUpdate AS (

SELECT

*,

Seq = ROW_NUMBER() OVER (ORDER BY Booking_Skey)

FROM Bookings

)

SELECT

Seq,

Booking_Skey,

BookingNumber = 'B' + RIGHT('0000000' + CAST(Seq AS VARCHAR(8)),8),

AnotherBookingNumber = 'B' + RIGHT('0000000' + CAST(Booking_Skey AS VARCHAR(8)),8)

FROM BookingsToUpdate

^{“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