我想在varchar数据类型的sql中自动增加列。 [英] I want to auto increment column in sql of varchar datatype..

问题描述

您好先生我有一个APPID varchar列，我想自动增加此列，如'ABCPQR000001'我怎么能在sql中执行此操作

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

推荐答案

试试这个：

Try this :

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

解决方案1。

Crete表格名称

Solution 1.
Crete a table Name

Auto_key(KeyName varchar(50 )PK,Value bigint,Prefix)
table_1 |1|ABCPQR

当您将数据插入Table_1时


获取Next-Id运行查询..

when you are inserting data to the Table_1
to get Next-Id Run the Query..

select Prefix+Format(Values+1,'000000') from Auto_key where KeyName='table_1'

并将此ID作为PK插入表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 ..

update Auto_key set Value=Value+1 where KeyName='table_1' 

解决方案2：

我认为你的表有一个标识列NameIdentity_coumn。

所以，你可以从身份价值获得PK（ABCPQR000001）。

使用..

当将数据插入table_1时，从表中选择Next_PK Id，如下所示..

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..

select 'ABCPQR'+Format(max(isnull(Identity_coumn,0)+1) from table_1

解决方案3：



你可以试试..

Solution 3:

you can try..

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

这篇关于我想在varchar数据类型的sql中自动增加列。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！