Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Creating a Ref Number usning letters and numbers. Expand / Collapse
Author
Message
Posted Wednesday, February 20, 2013 5:56 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 7, 2013 3:30 AM
Points: 62, Visits: 155
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
Post #1422041
Posted Wednesday, February 20, 2013 5:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:05 AM
Points: 5,231, Visits: 5,104
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




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1422042
Posted Wednesday, February 20, 2013 6:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 7,284, Visits: 13,824
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1422053
Posted Wednesday, February 20, 2013 5:57 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 10:55 PM
Points: 3,648, Visits: 5,321
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1422369
Posted Thursday, February 21, 2013 3:04 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 7, 2013 3:30 AM
Points: 62, Visits: 155
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.
Post #1422465
Posted Thursday, February 21, 2013 3:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 7,284, Visits: 13,824
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1422473
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse