Generate Alpha-Numeric Values in SQL SERVER

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

General SQL Server Forums

New to SQL Server Programming

Generate Alpha-Numeric Values in SQL SERVER

Author

Topic

sital
Yak Posting Veteran

89 Posts

Posted - 2009-07-04 : 06:53:08

Hi all,

I am creating a windows application. I have a column in the backend - MS sql server 2005 as ID which stores Patient Id.

The values for this column should start with
PT and contain the serial numbers in ascending order.

Eg.
PT001
PT002
PT003

I want to generate this automatically however identity does not allow this type of values.

Then how can I do this?

Is there any way I could do this automatically?

Please help me out!
Thanks in advance!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-04 : 07:05:37

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

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-05 : 02:53:50

create a calcualted column based on an identity column created in table

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-06 : 03:59:31

Also refer this
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=57069

Madhivanan

Failing to plan is Planning to fail

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-06 : 04:35:26

see this example


DECLARE @refNo  		VARCHAR(50),
@referencenumber		VARCHAR(32)

SELECT  @ReferenceNumber		= ''

SELECT @ReferenceNumber = MAX(empid)
FROM emptable

PRINT @ReferenceNumber

IF ISNULL(@ReferenceNumber,'') = ''
SELECT @refno = 'TR-'+ '01'
ELSE
SELECT @refno =  'TR-'+ RIGHT('00000' + CAST(CAST(RIGHT(@ReferenceNumber, 4) AS INT) + 1 AS VARCHAR(6)), 6)

SELECT @referencenumber = @refno
SELECT @referencenumber
--and then insert the @referencenumber into that column