sql函數整理--StringSplit

標籤：style   blog   http   color   os   io   for   art   

 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 @[email protected]+‘-‘[email protected]+‘-‘[email protected]+‘-‘+@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

 

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

彙總函式：對一組值執行計算並返回單個值

純量值函式：返回一個確定類型的標量值

資料表值函式：以表的形式返回一個傳回值