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.
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-Zfor 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 valHowever, 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 valThis 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. |
|
|
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-Zfor 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 itseehttp://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
nk
Starting Member
2 Posts |
Posted - 2014-01-03 : 04:59:29
|
Thank you Vishak and cgraus |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-01-03 : 07:36:04
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|