首页 > 代码库 > sql函数整理--StringSplit

sql函数整理--StringSplit

2024-07-15 01:17:27   222人阅读

 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 as18 begin 19     declare @Index int, @Entry nvarchar(max)20     set @Index = charindex(@Separator,@Input)21 22     while (@Index>0)23     begin24         set @Entry=ltrim(rtrim(substring(@Input, 1, @Index-1)))25         26         if (@RemoveEmptyEntries=0) or (@RemoveEmptyEntries=1 and @Entry<>‘‘)27             begin28                 insert into @TABLE([Value]) Values(@Entry)29             end30 31         set @Input = substring(@Input, @Index+datalength(@Separator)/2, len(@Input))32         set @Index = charindex(@Separator, @Input)33     end34     35     set @Entry=ltrim(rtrim(@Input))36     if (@RemoveEmptyEntries=0) or (@RemoveEmptyEntries=1 and @Entry<>‘‘)37         begin38             insert into @TABLE([Value]) Values(@Entry)39         end40 41     return42 end
splitstring

 

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)
how to use

注释：

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 OFF10 11 SELECT * FROM [USER]  WHERE a="netasp"12 13 SET QUOTED_IDENTIFIER OFF14 15 SELECT * FROM [USER]  WHERE a= ‘ netasp‘
example

=========================================================

 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 int11  )12  returns varchar(100)13  as14  begin15    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     begin21       set @strRack=‘0‘ +convert(varchar(2),@rack)22     end23    else24     set @strRack=convert(varchar(50),@rack)25     set @strFloor=convert(varchar(2),@floor)26     27    if(@position<10)28     begin29       set @strPosition=‘0‘+convert(varchar(2),@position)30     end31    else32     set @strPosition=convert(varchar(50),@position)33     set @strBit=convert(varchar(2),@bit)34     35     set @strReturn=@strRack+‘-‘+@strFloor+‘-‘+@strPosition+‘-‘+@strBit36     37     return @strReturn38  end
标量值函数
1  select dbo.[FormatLocaltionName]([row],[floor],[line],[bit])2  as localtionName,[row],[floor],[line],[bit] from [tes]
how to use

 

==================================================

聚合函数：对一组值执行计算并返回单个值

标量值函数：返回一个确定类型的标量值

表值函数：以表的形式返回一个返回值

 


联系
我们