/**********************************************************************************************
-- Title : [2k] 구분자로 구성된 싱글값을 멀티값으로 변환하는 함수
-- Reference : dBRang.com
-- Key word : 멀티값, 싱글값, 구분, 분리, pivot, unpivot, xml crosstab
**********************************************************************************************/
USE Tempdb
GO
-- Case 1.
CREATE FUNCTION fn_str_parse
( @str VARCHAR(100)
)
RETURNS @retTable TABLE ( seq INT IDENTITY(1,1)
, col VARCHAR(20))
AS
BEGIN
DECLARE @len INT
SET @len = LEN(@str)
WHILE (@len > 0)
BEGIN
IF charindex('///',@str) = 0
BEGIN
INSERT INTO @retTable VALUES (@str)
BREAK
END
ELSE
BEGIN
INSERT INTO @retTable VALUES (LEFT(@str,CHARINDEX('///',@str)-1))
SET @str = RIGHT(@str, @len-CHARINDEX('///',@str)-2)
SET @len = LEN(@str)
END
END
RETURN
END
GO
SELECT * FROM dbo.fn_str_parse('aaa///bbb///ccc')
SELECT * FROM dbo.fn_str_parse('111///222///333///')
-- Case 2.
DROP FUNCTION fn_parse_concat_str;
GO
CREATE FUNCTION dbo.fn_parse_concat_str
( @parsechar NVARCHAR(10)
, @source NVARCHAR(4000)
) RETURNS @list TABLE
( seq int identity(1,1) not null
, data NVARCHAR(4000)
)
BEGIN
DECLARE @data NVARCHAR(4000)
WHILE CHARINDEX(@parsechar,@source,0) <> 0
BEGIN
SET @data = RTRIM(LTRIM(SUBSTRING( @source, 1
, CHARINDEX(@parsechar, @source,0) - 1)));
SET @source = RTRIM(LTRIM(SUBSTRING(@source, CHARINDEX(@parsechar, @source, 0)
+ LEN(@parsechar), LEN(@source))));
IF LEN(@data) > 0
INSERT INTO @list VALUES (@data);
END
IF LEN(@source) > 0
INSERT INTO @list VALUES (@source);
RETURN;
END;
GO
SELECT * FROM dbo.fn_parse_concat_str('/', 'aaa/bbb/ccc')
SELECT * FROM dbo.fn_parse_concat_str('_', '_111_222_333_')
GO
-- Case 3. /* XML 사용 */
DECLARE @XmlDocumentHandle int
DECLARE @XmlDocument nvarchar(max)
SET @XmlDocument = N'<ROOT>
<email>
test1@naver.com
</email>
<email>
test2@naver.com
</email>
</ROOT>'
EXEC sp_xml_preparedocument @XmlDocumentHandle OUTPUT, @XmlDocument
SELECT *
FROM OPENXML (@XmlDocumentHandle, '/ROOT/email')
WITH (email nvarchar(50) '.')
EXEC sp_xml_removedocument @XmlDocumentHandle