SQL, remove appearances of a comma at end of the line

Question

My data looks as follows

MyText
-------
some text, some more text, even more text,,
some text,,,,
some text, some text,,,
some text, some more text, even more, yet more, and again

I would like to achieve:

MyText
-------
some text, some more text, even more text
some text
some text, some text
some text, some more text, even more, yet more, and again

How can I remove the commas at the end of the lines? I must retain the commas between items, but I need to remove any from the end

I need to do this within a select statement, and I haven't been able to find a solution of applying a RegEx without writing a function (Which I would prefer to avoid)

I have one solution, but its particulary dirty and I would like to improve it. I'm using a set of nested REPLACE to replace 4 commas with 3, 3 with 2, and 2 with one, then remove the end one

Any ideas?

EDIT: Data is coming from an external system, so I have no control over that, otherwise I'd concatenate with commas correctly in the first instance. This statement I'm using will be run on SQL Server 2005

Solution 1

Try something like this

DECLARE @Table TABLE(
        Val VARCHAR(MAX)
)

INSERT INTO @Table (Val)  SELECT 'some text, some more text, even more text,,'
INSERT INTO @Table (Val)  SELECT 'some text,,,,'
INSERT INTO @Table (Val)  SELECT 'some text, some text,,,'
INSERT INTO @Table (Val)  SELECT 'some text, some more text, even more, yet more, and again'

SELECT  Val,
        REVERSE(SUBSTRING(  REVERSE(Val),   PATINDEX('%[A-Za-z0-9]%',REVERSE(Val)), LEN(VAL) - (PATINDEX('%[A-Za-z0-9]%',REVERSE(Val)) - 1) ) ),
        *
FROM    @Table

Solution 2

Try this - it is similar to the given answers with the added benefit of supporting other characters in the text (some text, some text!,,, would not work correctly in the given solutions). It is also a bit shorter.

SELECT *, LEFT(val, LEN(val) - PATINDEX('%[^,]%', reverse(val)) + 1) FROM #TempTbl

For reference, you find the first non comma character in the reversed string and then right trim the initial string by that many places.

Solution 3

I think writing a sclar function will be a 'clean' solution for you.

Call your function, say RemoveCommasAtEnd(sqlLine)

And you simply call it within your select statement. In the function body, put in logic to remove the commas. If you need help with this, please let us know