Autoincrement in varchar value

hi Guys,
I've tangled in serious problem,
Is there any way in which we can autoincrement in
varchar value like i've one column in my table called PayCardId that should
be in 9 digit say '900001@.@.@.', now requirement is this to increment in same
format but it should automatically save in Database, for example
900001@.@.@.
.
.
900100@.@.@.
900101@.@.@.
900102@.@.@.
When it completes it's hundred series it should come in
thousand series like 901001@.@.@. and then
901002@.@.@.
901003@.@.@.
so on and so for
I'm sure there should be any way to
increment this, but i'm not able to think it this time, Please help me ASAP
Any help would be appriciated
ThanksHi
I am not sure whether this code will help you. if this is the logic. u can
cast the final value as string and insert into database.
CREATE TABLE #TempTable (CustID VARCHAR(9))
INSERT INTO #TempTable (CustID) VALUES ('900001ABC')
INSERT INTO #TempTable (CustID) VALUES ('900002XYZ')
INSERT INTO #TempTable (CustID) VALUES ('900003ABC')
DECLARE @.intNewValue INT
SELECT @.intNewValue = CAST(LEFT(MAX(CustID),6) AS INT) FROM #TempTable
SELECT @.intNewValue
SET @.intNewValue = @.intNewValue + 1
SELECT @.intNewValue
Thanks,
Ciju
"Manish Sukhija" wrote:

> hi Guys,
> I've tangled in serious problem,
> Is there any way in which we can autoincrement i
n
> varchar value like i've one column in my table called PayCardId that shoul
d
> be in 9 digit say '900001@.@.@.', now requirement is this to increment in sam
e
> format but it should automatically save in Database, for example
> 900001@.@.@.
> .
> .
> 900100@.@.@.
> 900101@.@.@.
> 900102@.@.@.
> When it completes it's hundred series it should come i
n
> thousand series like 901001@.@.@. and then
> 901002@.@.@.
> 901003@.@.@.
> so on and so for
> I'm sure there should be any way to
> increment this, but i'm not able to think it this time, Please help me ASA
P
> Any help would be appriciated
> Thanks
>|||Thanks a lot Ciju, it was realy helpful for me, i've got a good idea from
this code
thanks a lot again for giving quick response,
may god bless you
"Manish Sukhija" wrote:

> hi Guys,
> I've tangled in serious problem,
> Is there any way in which we can autoincrement i
n
> varchar value like i've one column in my table called PayCardId that shoul
d
> be in 9 digit say '900001@.@.@.', now requirement is this to increment in sam
e
> format but it should automatically save in Database, for example
> 900001@.@.@.
> .
> .
> 900100@.@.@.
> 900101@.@.@.
> 900102@.@.@.
> When it completes it's hundred series it should come i
n
> thousand series like 901001@.@.@. and then
> 901002@.@.@.
> 901003@.@.@.
> so on and so for
> I'm sure there should be any way to
> increment this, but i'm not able to think it this time, Please help me ASA
P
> Any help would be appriciated
> Thanks
>|||If you can touch on the table design yet..
then try this.
create TABLE [char_increment] (
[a] AS cast(id_num as varchar) + '@.@.@.',
[id_num] [bigint] IDENTITY (900000, 1) NOT NULL ,
[fname] [varchar] (20)
)
insert into char_increment (fname)
values ('a')
insert into char_increment (fname)
values ('b')
insert into char_increment (fname)
values ('c')
insert into char_increment (fname)
values ('d')