sql | SQL Server‎ > ‎sqlsrv | T-SQL‎ > ‎

tsql | SPLIT

USE [CORE_SYSTEM]
GO
/****** Object:  UserDefinedFunction [dbo].[fn_Split]    Script Date: 2012/12/14 上午 10:32:45 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
 
ALTER  FUNCTION [dbo].[fn_Split](@text nvarchar(MAX), @delimiter nvarchar(1) = N' ')
RETURNS @Strings TABLE
(   
  position int IDENTITY PRIMARY KEY,
  value Nvarchar(MAX)  
)
AS
BEGIN



DECLARE @index int
SET @index = -1

WHILE (LEN(@text) > 0)
  BEGIN 
    SET @index = CHARINDEX(@delimiter, @text) 
    IF (@index = 0) AND (LEN(@text) >= 0) 
      BEGIN  
        INSERT INTO @Strings (VALUE) VALUES (@text)
          BREAK 
      END 
    IF (@index >= 1) 
      BEGIN  
        INSERT INTO @Strings (VALUE) VALUES (--LEFT(@text, @index - 1))
CASE @index WHEN 1 THEN '' ELSE LEFT(@text, @index - 1) END)  
        SET @text = RIGHT(@text, (LEN(@text) - @index)) 
      END 
    ELSE
      SET @text = RIGHT(@text, (LEN(@text) - @index))
    END
  RETURN
END
 
Comments