sql 分割函数

ALTER FUNCTION [dbo].[Split](@InputStr nvarchar(max),@SplitBy nvarchar(20))
RETURNS  @tmpTable TABLE(       
    ID int IDENTITY PRIMARY KEY,     
    value nvarchar(max)   
)
AS
BEGIN
 DECLARE @Text nvarchar(max)
 DECLARE @iCount_InputStr int
 DECLARE @ilength_InputStr int

 DECLARE @SplitStrLength int
 SET @SplitStrLength = LEN(@SplitBy)

 IF NULLIF(@InputStr,'') IS NOT NULL
 BEGIN
  SET @iCount_InputStr = 1

  WHILE @iCount_InputStr<>0
  BEGIN
   SET @iCount_InputStr = PATINDEX('%['+ @SplitBy +']%',@InputStr)
   SET @ilength_InputStr = CASE @iCount_InputStr WHEN 0 THEN DATALENGTH(@InputStr) ELSE @iCount_InputStr-1 END
   SET @Text = SUBSTRING(@InputStr,1,@ilength_InputStr) 

   INSERT INTO @tmpTable
   (
    value
   )
   VALUES
   (
    @Text
   )
 
   SET @InputStr = SUBSTRING(@InputStr,@iCount_InputStr+@SplitStrLength,DATALENGTH(@InputStr))
  END
 END 
 RETURN
END

转载于:https://www.cnblogs.com/wanyuan8/archive/2011/09/13/2174896.html