Creating a Ref Number usning letters and numbers.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Creating a Ref Number usning letters and numbers.

Author

Topic

wafw1971
Yak Posting Veteran

75 Posts

Posted - 2013-02-20 : 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

52326 Posts

Posted - 2013-02-20 : 08:00:51

see

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

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-02-20 : 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

52326 Posts

Posted - 2013-02-20 : 08:24:22

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/

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-02-20 : 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

75 Posts

Posted - 2013-02-20 : 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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-20 : 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

75 Posts

Posted - 2013-02-20 : 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

52326 Posts

Posted - 2013-02-20 : 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

75 Posts

Posted - 2013-02-21 : 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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-02-21 : 04:45:52

hi,
In visakh's post, PK means Primary Key column name in your Table

--
Chandu

wafw1971
Yak Posting Veteran

75 Posts

Posted - 2013-02-21 : 05:01:31

Thank you so much everyone, it all works now.

Thanks

Wayne

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-02-21 : 05:04:52