| Author |
 Topic  |
|
|
wafw1971
Yak Posting Veteran
68 Posts |
 Posted - 02/20/2013 : 07:57:02
|
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 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47065 Posts |
|
|
bandi
Flowing Fount of Yak Knowledge
India
1419 Posts |
Posted - 02/20/2013 : 08:06:03
|
DECLARE @booking TABLE(BookId int)
INSERT INTO @booking VALUES(1), (12),(30)
SELECT 'B' + RIGHT( '00000000' + CAST(BookId AS VARCHAR), 8) FROM @booking
--
Chandu |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47065 Posts |
|
|
bandi
Flowing Fount of Yak Knowledge
India
1419 Posts |
Posted - 02/20/2013 : 08:37:27
|
I thought that bBookId is INT column.. so it won't cross 10 digits... While casting to VARCHAR by default it will take upto 30... right?
Thats why i haven't mentioned..
Yes your suggestion is also valuable...
Thank u so much...
--
Chandu |
|
|
|
wafw1971
Yak Posting Veteran
68 Posts |
Posted - 02/20/2013 : 11:21:26
|
Thanks for that, but I need to Update my table not insert it. The table already has 1.35 million rows of data. So it needs to start B00000001 and finish B01350000.
Thanks
Wayne |
|
|
|
James K
Flowing Fount of Yak Knowledge
1490 Posts |
Posted - 02/20/2013 : 11:27:44
|
Two things: 1) Are all the values in BookingNumber currently '0'? 2) Is the data type of BookingNumber column varchar or nvarchar? If you answered yes to both, your original query should have worked. Try this and see if it returns any rows at all:SELECT TOP (10) * FROM Bookings WHERE BookingNumber = '0' |
|
|
|
wafw1971
Yak Posting Veteran
68 Posts |
Posted - 02/20/2013 : 11:30:30
|
Hi James
Currently all the fields are NULLS and the BookingNumber Column is a VARCHAR(30.
Thanks
Wayne |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47065 Posts |
Posted - 02/20/2013 : 13:08:39
|
then it should be something like
USE Occupancy
Update t
Set BookingNumber = 'B' + Right ('00000000' + CAST (Seq AS varchar (30)), 8)
FROM (SELECT *,ROW_NUMBER() OVER (ORDER BY PK) AS Seq FROM Bookings)t
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
wafw1971
Yak Posting Veteran
68 Posts |
Posted - 02/21/2013 : 04:27:03
|
Morning visakh16
When I run your query I get the following error message:
Msg 207, Level 16, State 1, Line 4
Invalid column name 'PK'.
Do you know why?
Thanks
Wayne |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1419 Posts |
Posted - 02/21/2013 : 04:45:52
|
hi,
In visakh's post, PK means Primary Key column name in your Table
--
Chandu |
|
|
|
wafw1971
Yak Posting Veteran
68 Posts |
Posted - 02/21/2013 : 05:01:31
|
Thank you so much everyone, it all works now.
Thanks
Wayne |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1419 Posts |
Posted - 02/21/2013 : 05:04:52
|
quote:
Originally posted by wafw1971
Thank you so much everyone, it all works now.
Thanks
Wayne
Welcome
--
Chandu |
|
|
| |
Topic |
|
Creating a Ref Number usning letters and numbers.
