SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Creating a Ref Number usning letters and numbers.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

wafw1971
Yak Posting Veteran

75 Posts

Posted - 02/20/2013 :  07:57:02  Show Profile  Reply with Quote
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
52309 Posts

Posted - 02/20/2013 :  08:00:51  Show Profile  Reply with Quote
see


http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 02/20/2013 :  08:06:03  Show Profile  Reply with Quote
DECLARE @booking TABLE(BookId int)
INSERT INTO @booking VALUES(1), (12),(30)
SELECT 'B' + RIGHT( '00000000' + CAST(BookId AS VARCHAR), 8) FROM @booking

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/20/2013 :  08:24:22  Show Profile  Reply with Quote
quote:
Originally posted by bandi

DECLARE @booking TABLE(BookId int)
INSERT INTO @booking VALUES(1), (12),(30)
SELECT 'B' + RIGHT( '00000000' + CAST(BookId AS VARCHAR(10)), 8) FROM @booking

--
Chandu



Always specify a length when casting to varchar

see

http://visakhm.blogspot.in/2010/02/importance-of-specifying-length-in.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 02/20/2013 :  08:37:27  Show Profile  Reply with Quote
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
Go to Top of Page

wafw1971
Yak Posting Veteran

75 Posts

Posted - 02/20/2013 :  11:21:26  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3568 Posts

Posted - 02/20/2013 :  11:27:44  Show Profile  Reply with Quote
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'
Go to Top of Page

wafw1971
Yak Posting Veteran

75 Posts

Posted - 02/20/2013 :  11:30:30  Show Profile  Reply with Quote
Hi James

Currently all the fields are NULLS and the BookingNumber Column is a VARCHAR(30.

Thanks

Wayne
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/20/2013 :  13:08:39  Show Profile  Reply with Quote
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/

Go to Top of Page

wafw1971
Yak Posting Veteran

75 Posts

Posted - 02/21/2013 :  04:27:03  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 02/21/2013 :  04:45:52  Show Profile  Reply with Quote
hi,
In visakh's post, PK means Primary Key column name in your Table

--
Chandu
Go to Top of Page

wafw1971
Yak Posting Veteran

75 Posts

Posted - 02/21/2013 :  05:01:31  Show Profile  Reply with Quote
Thank you so much everyone, it all works now.

Thanks

Wayne
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 02/21/2013 :  05:04:52  Show Profile  Reply with Quote
quote:
Originally posted by wafw1971

Thank you so much everyone, it all works now.
Thanks
Wayne

Welcome

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000