1 SET ANSI_NULLS ON 2 GO 3 SET QUOTED_IDENTIFIER ON 4 GO 5 6 CREATE function [dbo].[SplitString] 7 ( 8 @Input nvarchar(max), 9 @Separator nvarchar(max)=‘,‘, 10 @RemoveEmptyEntries bit=1 11 ) 12 returns @TABLE table 13 ( 14 [Id] int identity(1,1), 15 [Value] nvarchar(max) 16 ) 17 as 18 begin 19 declare @Index int, @Entry nvarchar(max) 20 set @Index = charindex(@Separator,@Input) 21 22 while (@Index>0) 23 begin 24 set @Entry=ltrim(rtrim(substring(@Input, 1, @Index-1))) 25 26 if (@RemoveEmptyEntries=0) or (@RemoveEmptyEntries=1 and @Entry<>‘‘) 27 begin 28 insert into @TABLE([Value]) Values(@Entry) 29 end 30 31 set @Input = substring(@Input, @Index+datalength(@Separator)/2, len(@Input)) 32 set @Index = charindex(@Separator, @Input) 33 end 34 35 set @Entry=ltrim(rtrim(@Input)) 36 if (@RemoveEmptyEntries=0) or (@RemoveEmptyEntries=1 and @Entry<>‘‘) 37 begin 38 insert into @TABLE([Value]) Values(@Entry) 39 end 40 41 return 42 end
1 declare @str1 varchar(max), @str2 varchar(max), @str3 varchar(max) 2 3 set @str1 = ‘1,2,3‘ 4 set @str2 = ‘1###2###3‘ 5 set @str3 = ‘1###2###3###‘ 6 7 select [Value] from [dbo].[SplitString](@str1, ‘,‘, 1) 8 select [Value] from [dbo].[SplitString](@str2, ‘###‘, 1) 9 select [Value] from [dbo].[SplitString](@str3, ‘###‘, 0)
注释：
1.SET QUOTED_IDENTIFIER 为 ON 时，标识符可以由双引号分隔，而文字必须由单引号分隔。
1 SET QUOTED_IDENTIFIER ON 2 3 SELECT * FROM "USER" WHERE a=‘netasp‘ 4 5 SET QUOTED_IDENTIFIER ON 6 7 SELECT * FROM [USER] WHERE a=‘netasp‘ 8 9 SET QUOTED_IDENTIFIER OFF 10 11 SELECT * FROM [USER] WHERE a="netasp" 12 13 SET QUOTED_IDENTIFIER OFF 14 15 SELECT * FROM [USER] WHERE a= ‘ netasp‘
=========================================================
4个字段都是int型，需要前台把它们合成一个字段输出
1 set ANSI_NULLS ON 2 GO 3 SET QUOTED_IDENTIFIER ON 4 GO 5 CREATE FUNCTION [dbo].[FormatLocaltionName] 6 ( 7 @rack int, 8 @floor int, 9 @position int, 10 @bit int 11 ) 12 returns varchar(100) 13 as 14 begin 15 declare @strRack varchar(100),@strFloor varchar(100),@strPosition varchar(100),@strBit varchar(100) 16 17 declare @strReturn varchar(100) 18 19 if(@rack<10) 20 begin 21 set @strRack=‘0‘ +convert(varchar(2),@rack) 22 end 23 else 24 set @strRack=convert(varchar(50),@rack) 25 set @strFloor=convert(varchar(2),@floor) 26 27 if(@position<10) 28 begin 29 set @strPosition=‘0‘+convert(varchar(2),@position) 30 end 31 else 32 set @strPosition=convert(varchar(50),@position) 33 set @strBit=convert(varchar(2),@bit) 34 35 set @strReturn=@strRack+‘-‘+@strFloor+‘-‘+@strPosition+‘-‘+@strBit 36 37 return @strReturn 38 end
1 select dbo.[FormatLocaltionName]([row],[floor],[line],[bit]) 2 as localtionName,[row],[floor],[line],[bit] from [tes]
==================================================
聚合函数：对一组值执行计算并返回单个值
标量值函数：返回一个确定类型的标量值
表值函数：以表的形式返回一个返回值