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

Answer 1

Probably there is a better solution, but as a last resort you could use a loop like this:

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

(this works on SQL Server at least)

Answer 2

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

Answer 3

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

Answer 4

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

The following requires a set of exactly three nested REPLACE to 'squeezes' any number of multiple comma (CHAR(44)) characters to a single 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 which you already know how to do :)

Answer 5

Solution only using as single 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

Answer 6

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.