USE PUBS GO CREATE FUNCTION f_NextBH() RETURNS CHAR(7) AS BEGIN RETURN (SELECT RIGHT(10000001+ISNULL(MAX(ID),0),7) FROM TB) END GOCREATE TABLE Tb( ID CHAR(7) Default DBO.f_NextBH() , cVALUE VARCHAR(20) ) GOINSERT Tb(cVALUE) VALUES('The 1') INSERT Tb(cVALUE) VALUES('The 2') INSERT Tb(cVALUE) VALUES('The 3') SELECT * FROM TbGO DROP TABLE TB DROP FUNCTION Dbo.f_NextBH
USE PUBS
GO
CREATE FUNCTION f_NextBH()
RETURNS CHAR(7)
AS
BEGIN
RETURN (SELECT RIGHT(10000001+ISNULL(MAX(ID),0),7) FROM TB)
END
GOCREATE TABLE Tb(
ID CHAR(7) Default DBO.f_NextBH() ,
cVALUE VARCHAR(20)
)
GOINSERT Tb(cVALUE) VALUES('The 1')
INSERT Tb(cVALUE) VALUES('The 2')
INSERT Tb(cVALUE) VALUES('The 3')
SELECT * FROM TbGO
DROP TABLE TB
DROP FUNCTION Dbo.f_NextBH
请问你们所说的"显示的时候按LZ的那样显示",这个LZ是指什么啊?
在SQL Server 高手的大海中小心的行走
==================================
select RIGHT('000000'+LTRIM(RTRIM(CONVERT(VARCHAR,id))),6)
from table
[id] [numeric](18, 0) IDENTITY (1000000, 1) NOT NULL ,
[name] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
INSERT [dbo].[Table1]([name]) VALUES('1')SELECT RIGHT([id],6),[name] FROM [dbo].[Table1]GO
select right('00000'+ltrim(rtrim(convert(varchar,ID))),5)
from table
醒书网,免费精品电子书下载
www.wakebook.com
承诺:免费,无广告,链接全有效
---------------------------
ISNULL 替换值
再 RIGHT 取7位