MSSQL SPLIT 구현
/*
SELECT * FROM [dbo].[FN_SPLIT] ('감기,콧물,어지러움,콧물기침,열,가래,몸살',',')
*/
CREATE FUNCTION [dbo].[FN_SPLIT]
(
@PARAM VARCHAR(MAX) -- 대상문자열
, @DIV VARCHAR(1) -- 특정구분자
)
RETURNS
@RESULT TABLE
(
[VALUE] VARCHAR(MAX)
, [ORDER] INT
)
AS
BEGIN
DECLARE @INDEX INT
DECLARE @POS INT
DECLARE @ORDER INT
DECLARE @TEMP VARCHAR(MAX)
SET @INDEX = 1
SET @POS = 1
SET @ORDER = 1
WHILE @POS > 0
BEGIN
SET @POS = CHARINDEX(@DIV, @PARAM, @INDEX)
IF @POS = 0
BEGIN
SET @TEMP = RIGHT(@PARAM, LEN(@PARAM) - @INDEX + 1)
END
ELSE
BEGIN
SET @TEMP = SUBSTRING(@PARAM, @INDEX, @POS - @INDEX)
END
IF LEN(@TEMP) > 0
BEGIN
SET @TEMP = RTRIM(LTRIM(@TEMP))
INSERT INTO @RESULT VALUES (@TEMP, @ORDER)
END
SET @INDEX = @POS + 1
SET @ORDER = @ORDER + 1
END
RETURN
END
--실행결과--
SELECT * FROM [dbo].[FN_SPLIT] ('감기,콧물,어지러움,콧물기침,열,가래,몸살',',')
출처: http://mdj1234.tistory.com/38 [짬타이거 화이팅!]