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]