Click here to Skip to main content
15,946,988 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello sir I have a column APPID varchar that i want to do auto increment this column like 'ABCPQR000001' how can i do this in sql
Posted

Try this :

SQL
create table  #test
(
Id int identity(1,1),
Pid as case  len(Id) when 1 then 'ABCPQR-'+'00000'+convert(varchar,ID)
when 2 then 'ABCPQR-'+'0000'+convert(varchar,ID)
else 'ABCPQR-'+convert(varchar,ID)
end
)
 
INSERT INTO #test DEFAULT VALUES
INSERT INTO #test DEFAULT VALUES
select *From #test
 
Share this answer
 
Comments
jaket-cp 10-Dec-14 6:11am    
Like the idea :) 5ed
but it stops padding correctly after 100.
Consider this technique for padding
Pid as 'ABCPQR-'+REPLACE(STR(id, 6), SPACE(1), '0')
http://stackoverflow.com/questions/5540064/padleft-function-in-t-sql
King Fisher 10-Dec-14 6:21am    
just Modify the Query
when 3 then 'ABCPQR-'+'000'+convert(varchar,ID)
when 4 then 'ABCPQR-'+'00'+convert(varchar,ID)
when 5 then 'ABCPQR-'+'0'+convert(varchar,ID)
else 'ABCPQR-'+convert(varchar,ID)
;)
jaket-cp 10-Dec-14 6:26am    
I get it, it requires so much more typing :)
Solution 1.
Crete a table Name
C#
Auto_key(KeyName varchar(50 )PK,Value bigint,Prefix)
table_1 |1|ABCPQR


when you are inserting data to the Table_1
to get Next-Id Run the Query..
SQL
select Prefix+Format(Values+1,'000000') from Auto_key where KeyName='table_1'

and insert this ID to the Table Table_1 as PK

after insert data to the table_1,increment the value of table Auto_key ..
SQL
update Auto_key set Value=Value+1 where KeyName='table_1' 

Solution 2:
I think your table has a Identity Column NameIdentity_coumn.
so, you can get PK (ABCPQR000001) from the Identity Value.
Use..
when inserting data to the table_1 select Next_PK Id from table like this..
SQL
select 'ABCPQR'+Format(max(isnull(Identity_coumn,0)+1) from table_1


Solution 3:

you can try..
SQL
select 'ABCPQR'+Format(substring(isnull(PKColumn,'000000000000'),7,len(isnull(PKColumn,'000000000000'))-7)+1,'000000') from Table_1

here 7 is the starting position of int_value in PKColumn of the table_1
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900