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
 procedure for incrementing the number based on alp

Author  Topic 

nk
Starting Member

2 Posts

Posted - 2013-12-28 : 12:58:16
Hi everyone,

I am new to T-SQL. So,Can u please help me in writing the procedure for incrementing the number based on alphabets from A-Z

for example:
if alphabet is A then increment should be A001,next A002..etc
if alphabet is B then increment should be B001,next B002...etc
.
.
.
.
if alphabet is Z then increment should be Z001,next Z002...etc

cgraus
Starting Member

12 Posts

Posted - 2013-12-28 : 15:51:11
This depends on where the values are stored. One idea would be to have an identity column and a letter column, so your values would be A1, A2, A3, etc. Then you could do this in SS2012:

create table val
(
Letter char,
id int identity
)

insert into val (letter) values ('a'), ('a'), ('a'), ('a')

select letter + FORMAT(id,'#####') AS newId from val

However, I would suggest you're better off doing the formatting part in your presentation layer. The advantages of splitting out the letter include that you can use an identity column and won't have to worry about wrapping any insert in a transaction, in case two inserts happen at once, the efficiences that come from SQL Server knowing your exact format, and enforcing your format ( a varchar column would be difficult to parse for a next value, and would accept any string value ).

If you really want to return the full 'number' from SQL, you can do this:

select letter + RIGHT('0000' + CAST(id AS NVARCHAR), 4) AS newId from val

This works by putting enough 0s in place to work with an empty string, and grabbing four characters from the right, so the extra 0s are discarded.


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-29 : 12:41:46
quote:
Originally posted by nk

Hi everyone,

I am new to T-SQL. So,Can u please help me in writing the procedure for incrementing the number based on alphabets from A-Z

for example:
if alphabet is A then increment should be A001,next A002..etc
if alphabet is B then increment should be B001,next B002...etc
.
.
.
.
if alphabet is Z then increment should be Z001,next Z002...etc


you can do this by creating an identity column and then created a computed column out of it
see
http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

nk
Starting Member

2 Posts

Posted - 2014-01-03 : 04:59:29
Thank you Vishak and cgraus
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-03 : 07:36:04
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -