MSSQL SPLIT FUNCTION

Developer

MSSQL SPLIT FUNCTION

Angeleyes 2009. 7. 10. 10:18

function 입니다~
split....

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		고지현
-- Create date: 2009-07-10
-- Description:	SPLIT Function
/*
	SELECT * FROM [Symp].dbo.UF_TEXT_SPLIT('test,test1,test2,test3', ',')
*/
-- =============================================
ALTER FUNCTION [dbo].[UF_TEXT_SPLIT]
(
	-- Add the parameters for the function here
	@TEXT		VARCHAR(100)
	,@DELIMITER	CHAR(1)
)
RETURNS @STRINGS TABLE
(
	POSITION INT IDENTITY(1,1) PRIMARY KEY, 
	VALUE NVARCHAR(MAX)
)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @INDEX	INT
	
	-- Add the T-SQL statements to compute the return value here
	SET	@INDEX = -1
	
	WHILE (LEN(@text) > 0)
	BEGIN
		SET @INDEX = CHARINDEX(@DELIMITER , @TEXT)
		
		IF (@INDEX = 0) AND (LEN(@TEXT) > 0)
		BEGIN
			INSERT INTO @STRINGS VALUES (@TEXT)
			BREAK
		END
		
		IF (@INDEX > 1)
		BEGIN
			INSERT INTO @STRINGS VALUES (LEFT(@TEXT, @INDEX - 1))
			SET @TEXT = RIGHT(@TEXT, (LEN(@TEXT) - @INDEX))
		END
		ELSE
			SET @TEXT = RIGHT(@TEXT, (LEN(@TEXT) - @INDEX)) 
	END

-- Return the result of the function
	RETURN

END

저작자표시 비영리 변경금지 (새창열림)