在sql服务器中自动处理criment id [英] auto in criment id in sql sever

问题描述

我正在使用C＃.net和sql server.i进行一个项目。可以在数据库表中自动增加coulum值。

但我想他们看起来像emp0001，emp002 ....



我怎么能这样做？

i am doin a project using C#.net and sql server.i could auto increment coulum values in the database table.
but i wann them look like emp0001,emp002....

how can i make it like this?

推荐答案

使用自动增量功能但只提取额外信息时你查询数据库....

Use the autoincrement feature but just extract the extra info when you query the database ....

-- Create a sample table
CREATE TABLE #chill60
(
    ID int IDENTITY(1,1),
    TXT varchar(20)
)
-- insert some sample data (VS2005)
insert into #chill60 select *  from
(select top 10 (char(abs(checksum(NEWID())) % 26 + 65) + CHAR(ABS(CHECKSUM(NEWID())) % 26 + 65)) AS TXT
FROM syscolumns ac1 cross join syscolumns ac2 cross join syscolumns ac3) as a

然后使用这样的东西以你想要的格式恢复数据

Then use something like this to get the data back in the format you want

SELECT 'emp'+replace(str(ID, 7), ' ', '0') as empid, TXT
FROM #chill60

返回

Returns

empid       TXT
emp0000001  VD
emp0000002  AI
emp0000003  AS
emp0000004  EB
emp0000005  XU
emp0000006  UR
emp0000007  IW
emp0000008  IA
emp0000009  YS
emp0000010  US

你可以把''emp''+替换（str（ID，7），''''，''0 ''）位到一个函数中，或者甚至有一个数据库触发器，它根据自动递增的ID用这个派生值填充另一个列。使用内置功能而不是试图自己管理ID有很多好处

You could put the ''emp''+replace(str(ID,7), '' '',''0'') bit into a function or even have a database trigger that populates a-n-other column with this derived value based on the auto-incremented ID. There are advantages to using built-in functionality rather than trying to manage the IDs yourself

您好，



检查脚本..

Hi,

Check the Script..

SELECT 'emp'+RIGHT('0000'+CAST((ROW_NUMBER() OVER(ORDER BY EmpID)) AS VARCHAR(10)),4)
FROM EmpDetails

问候，

GVPrabu

Regards,
GVPrabu

这篇关于在sql服务器中自动处理criment id的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！