Using Text in Field with Identity Specification

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

Using Text in Field with Identity Specification

Author

Topic

benking9987
Posting Yak Master

124 Posts

Posted - 2009-02-16 : 19:11:20

I need to create a column that serves as an identity specification but ALSO contains text. i.e. I need the values to end up like:

AZPU-000001
AZPU-000002
AZPU-000003
AZPU-000004

etc.

Does anyone have any ideas how to get the "AZPU-" or similar text to start the field?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-16 : 19:19:00

You can't use the identity option here. You'll need to write custom code to handle that.

But I would recommend that you just use two columns instead. The first column would contain AZPU-, or whatever needs to be the prefix and the second column would use the identity option. In the front end application, you'd concatenate them together to form your string. You might need to pad zeroes in between as well, depends on your business requirement.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-17 : 00:03:54

try like this



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

SELECT  @ReferenceNumber		= ''

SELECT @ReferenceNumber = MAX(columnname)
FROM urtablename

PRINT @ReferenceNumber

IF ISNULL(@ReferenceNumber,'') = ''
SELECT @refno = 'AZPU-'+ '000001'
ELSE
SELECT @refno =  'AZPU-'+ 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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-02-17 : 01:53:25

Also refer this

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

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-17 : 02:56:30

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources