TSQL Removing last comma from concatenated string

Question

I've a column with concatenated values, but the string comes with a comma at the end. How can I remove the last comma on existing values?

SELECT 

    m.Mais_Id
    , m.Outro

    (SELECT CAST(emp.First_Name + ' ' + emp.Last_Name + ', ' AS VARCHAR(MAX)) 
    FROM 
        Patos p
    LEFT JOIN 
        Employee emp
    ON
        p.Pato_Id = emp.Pato_Id
        WHERE
        m.Pato_Id = p.Pato_Id
    FOR XML PATH ('')
    ) AS Names
FROM    
    Mais m

I've this:

Mais_Id Outro Names
0       As    Adn Meas, Fjds Poi, Csa Drop,
1       Be    
2       Tee   As Been,

This is the pretended result:

Mais_Id Outro Names
0       As    Adn Meas, Fjds Poi, Csa Drop
1       Be    
2       Tee   As Been

Take a look to [Simulating group\_concat MySQL function in Microsoft SQL Server 2005?](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005), last char (,) is truncated. — dani herrera, Oct 01 '13 at 11:46

Roman Pekar · Answer 1 · 2013-10-01T11:56:19.960

Most neat way to do this is to use stuff() function:

stuff(
    (
        select ', ' + cast(emp.First_Name + ' ' + emp.Last_Name as varchar(max)) 
        from Patos as p
            left outer join Employee as emp on p.Pato_Id = emp.Pato_Id
        where m.Pato_Id = p.Pato_Id
        for xml path(''), type
    ).value('.', 'nvarchar(max)')
, 1, 2, '')  as Names

Note it's also safer to get concatenated string with value() function, so if you have special characters like & or < it will be properly shown.

score 0 · Answer 2 · answered Oct 01 '13 at 11:44

Try this:

SELECT Mais_id, m.Outro, Substring(newColumn, 0, LEN(newColumn) - 1)
FROM (

SELECT 

    m.Mais_Id
    , m.Outro

    (SELECT CAST(emp.First_Name + ' ' + emp.Last_Name + ', ' AS VARCHAR(MAX)) as newColumn
    FROM 
        Patos p
    LEFT JOIN 
        Employee emp
    ON
        p.Pato_Id = emp.Pato_Id
        WHERE
        m.Pato_Id = p.Pato_Id
    FOR XML PATH ('')
    ) AS Names
FROM    
    Mais m
)

TSQL Removing last comma from concatenated string

2 Answers2