SqlServer Split函數

標籤：blog   io   使用   ar   strong   div   cti   sp   log   

Create FUNCTION [dbo].[SplitToTable] (     @SplitString nvarchar(max),       @Separator nvarchar(10)=‘ ‘ ) RETURNS @SplitStringsTable TABLE ( [id] int identity(1,1), [value] nvarchar(max) )  AS  BEGIN       DECLARE @CurrentIndex int;        DECLARE @NextIndex int;     DECLARE @ReturnText nvarchar(max);          SELECT @CurrentIndex=1;          WHILE(@CurrentIndex<=len(@SplitString))               BEGIN                        SELECT @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex);                          IF(@NextIndex=0 OR @NextIndex IS NULL)                                   SELECT @NextIndex=len(@SplitString)+1;                                SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@[email protected]);                            INSERT INTO @SplitStringsTable([value]) VALUES(@ReturnText);                             SELECT @[email protected]+1;                END           RETURN;   END

　　使用

declare @TR NVARCHAR(50)
declare @TRS NVARCHAR(1000)
SET @TRS=‘123,456,789‘
set @TR=(select top 1 VALUE from dbo.SplitToTable(@TRS,‘,‘) order by NEWID()) --最外面一定要加括弧
PRINT(@TR)

SqlServer Split函數