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 |
sql | SQL Server > sqlsrv | T-SQL >