如何在msSQL中为标识列字符加前缀

Question

如何更改表的主标识列，使其在数字之前包含一个字母？我有一个名为vendor_master的表，其中有一个主键VendorID，我想将其存储为v1、v2、v3等

CREATE TABLE [dbo].[vendor_master](
[vendorID] [int] IDENTITY(1,1) NOT NULL,
[Vname] [nvarchar](50) NOT NULL,
[Email] [nvarchar](50) NOT NULL,
[Mobile] [bigint] NULL,
[Landline] [bigint] NULL,
[Address] [nvarchar](max) NOT NULL,
[Pincode] [int] NOT NULL)

Answer 1

如果表中包含字段对您来说很重要，请尝试以下操作：

CREATE TABLE [dbo].[vendor_master]
  (
    [vendorID] [int] IDENTITY(1,1) NOT NULL,
    [Vname] [nvarchar](50) NOT NULL,
    [Email] [nvarchar](50) NOT NULL,
    [Mobile] [bigint] NULL,
    [Landline] [bigint] NULL,
    [Address] [nvarchar](max) NOT NULL,
    [Pincode] [int] NOT NULL,
    [VendorKey]  AS ('v' +CONVERT([varchar](10),[vendorID])) PERSISTED
  )

当然，在排序时会产生一些奇怪的结果，所以你可以考虑用零填充数据，使其长度一致：

CREATE TABLE [dbo].[vendor_master]
  (
    [vendorID] [int] IDENTITY(1,1) NOT NULL,
    [Vname] [nvarchar](50) NOT NULL,
    [Email] [nvarchar](50) NOT NULL,
    [Mobile] [bigint] NULL,
    [Landline] [bigint] NULL,
    [Address] [nvarchar](max) NOT NULL,
    [Pincode] [int] NOT NULL,
    [VendorKey]  AS ('v' +RIGHT('000000000' + CONVERT([varchar](10),[vendorID]),10)) PERSISTED
  )

Answer 2

你实际上不能按照你的要求去做。无论如何，没有理由在字段值的前面添加相同的字母。相反，在SELECT语句中添加字母：

SELECT CONCAT('v', vendorID) AS VendorIDWithPrefix, Vname, Email, Mobile
FROM vendor_master