如何在sql server 2008 r2中增加varchar [英] how to increment varchar in sql server 2008 r2
问题描述
如何在服务器中增加varchar。
例如:refid0001,refid0002,refid0003
我想自动增加值,当我在表中插入记录
而不是使用varchar,您可以使用Int作为列数据类型并应用IDENTITY(Property) [ ^ ]这将自动为您增加值。然后,您可以将其转换为Varchar并连接所需的字符串。
这是一个示例
CREATE TABLE #tbUser
(
UserID INT IDENTITY ( 1 , 1 ),
UserName VARCHAR ( 100 )
)
INSERT INTO #tbUSer
SELECT ' User1' UNION
SELECT ' User2' UNION
SELECT ' User3' UNION
SELECT ' User4'
SELECT ' refid' + RIGHT (REPLICATE( 0 , 4 )+ CAST(UserID AS Varchar ), 4 ) AS NewCol,* FROM #tbUser
DROP TABLE #tbUser
最好的方法,创建一个函数DBO.GetSrNo()
ALTER 功能 DBO.GetSrNo( )
RETURNS nvarchar ( 10 )
as
开始
声明 < span class =code-sdkkeyword> @ C_No as int , @ val as nvarchar ( 5 )
SET @ C_No =(选择 COUNT(*)+ 1 来自 T2)
SET @ val = CONVERT ( nvarchar ( 10 ),( SELECT SUBSTRING(' 0000',Len( @ C_No ), Len(' 0000') - Len( @ C_No )))+ convert ( nvarchar ( 10 ), @ C_No ))
返回 @ val
结束
之后创建一个表并将DBO.GetSrNo()函数设置为默认值。所以它会自动填充。
CREATE TABLE [dbo]。[T2](
[DOC_NO] [ nvarchar ]( 20 ) NULL ,
[E_NAME] [ nvarchar ]( 50 ) NULL
) ON [ PRIMARY ]
GO
ALTER 表 [dbo]。[T2] ADD CONSTRAINT [DF_T2_DOC_NO] DEFAULT ([DBO]。[GetSrNo]()) FOR [DOC_NO]
GO
插入时...
INSERT INTO T2(E_NAME)值(' VIJAY')
INSERT INTO T2(E_NAME)值(' VINOD')
INSERT INTO T2(E_NAME) )值(' AJAY')
输出
SELECT * FROM T2
DOC_NO E_NAME
---- ------ --------
0001 VIJAY
0002 VINOD
0003 AJAY
我希望你喜欢这样...新年快乐...祝你好运.. :)
问候,
Vijay
方法#1
你不能自动加入varchar列
创建一个整数列并将其设置为autoincr而你要显示,
使用如下查询......
SELECT''refid''+ RIGHT(REPLICATE(0,4-len) (Id))+转换(varchar,Id)来自tblnm
- - - - - - - - - - - - - - - 要么 - - - - - - - - - - -----------
方法#2
创建可以增加varchar的函数1
写查询
- 插入查询
insert into tbl(id)选择( select dbo.incr(max(id))来自 tbl)
- 简单选择查询
选择 dbo.incr(' refid0002')
- o / p = refid0003
创建功能
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo]。[ParseValues]
( @ String varchar ( 8000 ),< span class =code-sdkke yword> @Delimiter varchar ( 10 ))
RETURNS @ RESULTS TABLE (ID int identity ( 1 , 1 ),Val varchar ( 50 ))
AS
BEGIN
DECLARE < span class =code-sdkkeyword> @ Value varchar ( 100 )
WHILE @ String 不 null
BEGIN
SELECT @ Value = CASE WHEN PATINDEX('' %' + @Delimiter + ' %', @ String )> 0 那么 LEFT ( @ String ,PATINDEX(' %' + @Delimiter + ' %', @ String ) - 1) ELSE @ String END ,@ String = CASE WHEN PATINDEX(' %' + @Delimiter + ' %', @ String )> 0 那么 SUBSTRING( @ String ,PATINDEX(< span class =code-string>' %' + @Delimiter + ' %', @ String )+ LEN( @Delimiter ),LEN( @ String )) ELSE NULL END
INSERT INTO @ RESULTS (Val)
SELECT @Value
END
返回
END
创建此功能
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
- 选择dbo.Incr('refid0002')
ALTER 功能 [dbo]。 [Incr]
(
@ a varchar (max)
)
RETURNS varchar (max)
AS
BEGIN
选择 @ a = val < span class =code-keyword>来自 [dbo]。[ParseValues]( @ a ,' refid')
选择 @ a = convert ( varchar ,( convert ( numeric , @ a )+ 1))
选择 @ a = ' refId' + replicate(' 0',4-len( @ a ))+ @ a
return @ a
END
快乐编码!
:)
How to increment varchar in server.
For example:refid0001,refid0002,refid0003
I want to increment values automatically,when i insert records in table
Instead of using varchar you can use Int as the column datatype and apply the IDENTITY (Property)[^] which will auto increment the value for you. You can then convert this to Varchar and concatenate the required string.
Here is a sample
CREATE TABLE #tbUser ( UserID INT IDENTITY(1,1), UserName VARCHAR(100) ) INSERT INTO #tbUSer SELECT 'User1' UNION SELECT 'User2' UNION SELECT 'User3' UNION SELECT 'User4' SELECT 'refid' + RIGHT(REPLICATE(0,4) + CAST(UserID AS Varchar),4) AS NewCol,* FROM #tbUser DROP TABLE #tbUser
Best way to do that, Create a function DBO.GetSrNo()
ALTER Function DBO.GetSrNo() RETURNS nvarchar(10) as Begin Declare @C_No as int , @val as nvarchar(5) SET @C_No = (Select COUNT(*)+1 from T2) SET @val = CONVERT(nvarchar(10), (SELECT SUBSTRING('0000',Len(@C_No),Len('0000')-Len(@C_No))) + convert (nvarchar(10),@C_No)) Return @val End
After that create a table and set DBO.GetSrNo() function as default. So It will be fill automatically.
CREATE TABLE [dbo].[T2]( [DOC_NO] [nvarchar](20) NULL, [E_NAME] [nvarchar](50) NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[T2] ADD CONSTRAINT [DF_T2_DOC_NO] DEFAULT ([DBO].[GetSrNo]()) FOR [DOC_NO] GO
When you insert ...
INSERT INTO T2 (E_NAME) values ('VIJAY') INSERT INTO T2 (E_NAME) values ('VINOD') INSERT INTO T2 (E_NAME) values ('AJAY')
Output
SELECT * FROM T2
DOC_NO E_NAME
---------- --------
0001 VIJAY
0002 VINOD
0003 AJAY
I hope u like this ...Happy New Year ... wish u good luck .. :)
Regards,
Vijay
Method #1
you can not auto incr varchar column
make a integer column and set it autoincr while you want to display,
use query as below...
SELECT ''refid'' + RIGHT(REPLICATE(0,4-len(Id)) + convert(varchar,Id) from tblnm
------------------------------OR------------------------------
Method #2
Create functions that can increment varchar with 1
write query
--insert query insert into tbl(id) select (select dbo.incr(max(id)) from tbl) --simple select query select dbo.incr('refid0002') --o/p = refid0003
create function
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[ParseValues] (@String varchar(8000), @Delimiter varchar(10) ) RETURNS @RESULTS TABLE (ID int identity(1,1), Val varchar(50)) AS BEGIN DECLARE @Value varchar(100) WHILE @String is not null BEGIN SELECT @Value=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN LEFT(@String,PATINDEX('%'+@Delimiter+'%',@String)-1) ELSE @String END, @String=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN SUBSTRING(@String,PATINDEX('%'+@Delimiter+'%',@String)+LEN(@Delimiter),LEN(@String)) ELSE NULL END INSERT INTO @RESULTS (Val) SELECT @Value END RETURN END
Create this function also
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO --select dbo.Incr('refid0002') ALTER FUNCTION [dbo].[Incr] ( @a varchar(max) ) RETURNS varchar(max) AS BEGIN select @a=val from [dbo].[ParseValues](@a,'refid') select @a= convert(varchar,(convert(numeric,@a)+1)) select @a= 'refId' + replicate('0',4-len(@a)) + @a return @a END
Happy Coding!
:)
这篇关于如何在sql server 2008 r2中增加varchar的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!