MSSQL 연결 문자열을 row로 변환
function으로..[code sql]
CREATE FUNCTION [dbo].[SplitToRows]
(
@List NVARCHAR(MAX),
@delim VARCHAR(255)
)
RETURNS TABLE
AS
RETURN ( SELECT [Value] FROM
(
SELECT
[Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],
CHARINDEX(@delim, @List + @delim, [Number]) - [Number])))
FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)
FROM sys.all_objects) AS x
WHERE Number <= LEN(@List)
AND SUBSTRING(@delim + @List, [Number], LEN(@delim)) = @delim
) AS y
);
[/code]
임시 테이블 방법으로...
[code sql]
DECLARE @to_convert_str nvarchar(2000)
DECLARE @delim varchar(1)
set @to_convert_str = '가나다,라마바,사아자';
set @delim = ','
;WITH converte_to_rows AS (
SELECT
[converted_row] = LTRIM(RTRIM(SUBSTRING(@to_convert_str, [Number],
CHARINDEX(@delim, @to_convert_str + ',', [Number]) - [Number])))
FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)
FROM sys.all_objects) AS x
WHERE Number <= LEN(@to_convert_str)
AND SUBSTRING( ',' + @to_convert_str, [Number], LEN( @delim)) = @delim
)
SELECT * FROM converte_to_rows
[/code]
Trackback Address:이 글에는 트랙백을 보낼 수 없습니다