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

Question

My data is 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 to remove commas at the end of lines? I have to keep commas between the elements, but I need to remove any of the end.

I need to do this in the select statement, and I could not find a solution for applying RegEx without writing a function (which I would prefer to avoid)

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

Any ideas?

EDIT: The data comes from an external system, so I do not control this, otherwise I would first combine the commas. This statement that I use will be run on SQL Server 2005

Answer 1

-

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

Answer 2

- (- , - !, ). .

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

, .

Answer 3

, sclar "" .

, RemoveCommasAtEnd(sqlLine)

select. . ,

Answer 4

, , :

while RIGHT(@theString, 1)=','
  select @theString=SUBSTRING(@theString, 1, LEN(@theString)-1)

( SQL Server)

Answer 5

I am using a nested REPLACE set to replace 4 commas 3, 3 with 2 and 2 with one

The following requires that a set of three nested REPLACEcompress any number of semicolons ( CHAR(44)) to one character:

WITH MyTable (MyText)
AS
(
 SELECT 'some text, some more text, even more text,,'
 UNION ALL
 SELECT 'some text,,,,'
 UNION ALL
 SELECT 'some text, some text,,,'
 UNION ALL
 SELECT 'some text, some more text, even more, yet more, and again'
)
SELECT REPLACE(
                REPLACE(
                        REPLACE(
                                MyText,CHAR(44), CHAR(44) + CHAR(22)
                               ), CHAR(22) + CHAR(44), ''
                      ), CHAR(22), ''
              ) AS MyText_commas_squeezed
  FROM MyTable;

Now you just need to trim any trailing single comma that you already know how to do :)

Answer 6

A solution using only one REVERSE.

SUBSTRING (myText, 1, LEN (myText) - PATINDEX ('% [A-Za-z0-9]%', REVERSE (myText)) + 1)

Demo:

WITH MyTable (MyText)
AS( 
              SELECT 'some text, some more text, even more text,,' 
    UNION ALL SELECT 'some text,,,,' 
    UNION ALL SELECT 'some text, some text,,,' 
    UNION ALL SELECT 'some text, some more text, even more, yet more, and again'
  )

SELECT SUBSTRING(myText, 1 , LEN(myText) - PATINDEX('%[A-Za-z0-9]%', REVERSE(myText)) + 1 ) from MyTable