특정 문자로 구분되는 데이터 분리 Query (split)

특정 문자로 구분되는 데이터 분리 Query

DECLARE     @p VARCHAR(MAX)
SET        @p = '0001,0003,0005'

IF @p IS NOT NULL OR @p != ''

BEGIN

        IF RIGHT(@p, 1) = @delimiter

        BEGIN

               SET @p = LEFT(@p, LEN(@p) - 1)

        END

END    

SELECT    CONVERT(VARCHAR(10), Y.item.query('text()')) AS val
FROM        (
            SELECT CONVERT(xml, '<r>' + replace(@p, ',', '</r><r>') + '</r>') AS xitem
        ) X
        CROSS APPLY X.xitem.nodes('/r') AS Y(item)

 

The table is example is:
TableName: NumberTable

NumberCols

first

second

third

fourth

fifth

Output : first,second,third,fourth,fifth

Option 1: This is the smartest way.
DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+',' , '') + NumberCols
FROM NumberTable
SELECT @listStr
Please make a note that COALESCE returns the first NOT NULL value from the argument list we pass.

Option 2: This is the smart but not the best way; though I have seen similar code many times.
DECLARE @listStr VARCHAR(MAX)
SET @listStr = ''
SELECT @listStr = @listStr + NumberCols + ','
FROM NumberTable
SELECT SUBSTRING(@listStr , 1, LEN(@listStr)-1)