Sql:Remove Last Comma in String

SQL : remove last comma in string

Using REVERSE and STUFF:

SELECT
    REVERSE(
        STUFF(
            REVERSE(LTRIM(RTRIM(INETSHORTD))), 
            1, 
            CASE WHEN SUBSTRING((REVERSE(LTRIM(RTRIM(INETSHORTD)))), 1, 1) = ',' THEN 1 ELSE 0 END, 
            ''
        )
    )
FROM tbl

---

First, you want to TRIM your data to get rid of leading and trailing spaces. Then REVERSE it and check if the first character is ,. If it is, remove it, otherwise do nothing. Then REVERSE it back again. You can remove the first character by using STUFF(string, 1, 1, '').

SQL Fiddle

Need help removing the last comma from the query

Just add a RTRIM(...,',')..

RTRIM(
        LTRIM(
            MAX(SYS_CONNECT_BY_PATH(person.Last_Name || ' ' || person.First_Name || ', ', '<br/>')
        ),
        ','
    )

Removing leading and trailing commas

Just another option.

This is a non-destructive approach that will eliminate any number of repeating commas and forces a final cleanup via the double pipes

For the expansion,reduction, and elimination I picked two obscure characters †‡

Example

Declare @S varchar(max) =',,,,Some,,,,,Content,,,'

Select
    replace(
        replace(
            replace(
                replace(
                    replace('||,' + @S + ',||', ',', '†‡'), 
                    '‡†', ''
                ),
                '†‡', ','
            ),
            '||,', ''
        ),
        ',||', ''
    )

Returns

Some,Content

EDIT - Removed the LTRIM()/RTRIM()

SQL Server: how to remove last comma after combining rows using XML Path

declare  @BigStrRes8K nvarchar(4000) 

SELECT @BigStrRes8K = ( SELECT top (2) [type] + ', ' AS 'data()'
FROM supportContacts 
ORDER BY type DESC
FOR XML PATH('') ) 

SELECT LEFT(RTRIM(@BigStrRes8K), ( LEN(RTRIM(@BigStrRes8K))) - 1) as FinalNoComma

I would never do this where I controlled the render code. I would teach the caller to handle the trailing comma. Also you have to allow for nulls and the 4K or 8K limit of SQL rows

SQL Remove last comma from Concatenated Columns

I assume you are using SQL Server, based on the syntax.

I wouldn't do this by removing the last comma. I would do this by removing the first comma using stuff():

SELECT (CASE WHEN intExtraTime > 0 or intprocessor = 1 or intRest = 1 or
                 intReader = 1 or intScribe = 1
             THEN STUFF(COALESCE(CASE WHEN intExtraTime > 0 THEN ', ' + Convert(varchar(3), intExtraTime) + '% Extra Time' END,  '')
                        COALESCE(CASE WHEN intprocessor = 1 THEN ', Laptop' END, '') +
                        COALESCE(CASE WHEN intRest = 1 THEN ', Rest Break' END, '') + 
                        COALESCE(CASE WHEN intReader = 1 THEN ', Reader' END, '') +
                        COALESCE(CASE WHEN intScribe = 1 THEN ', Scribe' END, ''),
                        1, 2, '')
       END)
FROM dbo.Candidate ExamOptions;

Remove trailing comma from xml path

Usually you use the FOR XML PATH query as a sub query and put the comma at the beginning instead of end so that it is easier to STUFF:

SELECT STUFF((
    SELECT ',' + CONVERT(varchar(10), clientid)
    FROM daily
    FOR XML PATH('')
), 1, 1, '')

Trimming off last character if it is a comma SQL

Include a case statement to check if @MASTR is blank within each condition check, as following:

IF @PATSTR LIKE '%b%'
SET @MASTR = @MASTR
  + CASE WHEN LEN(@MASTR) > 0 THEN ', ' ELSE '' END
  + CASE WHEN @PATSTR LIKE '%b' then 'OR ' ELSE '' END
  + 'Check'

Remove the last value after a comma in nvarchar SQL

OK, the aforementioned solutions seemed like overkill, so I was able to find an easier solution that worked. Here is what I used generically:

 UPDATE [dbo].[table1]
 SET [Col2] = left (Col1, len(Col1) - charindex(',', reverse(Col1) + ','))

Very similar to the second solution above but with the ',' added at the end. This produced the desired result.

Strip the last dynamic column from ending comma?

Don't put the commas at the end, put them at the start, and then strip the first character, using STUFF, that's far easier in T-SQL.

So instead of {Expression} + ',' do ',' + {Expression}. Then you can simply do STUFF(@columnList,1,1,'') to remove the leading comma instead.

---

Related Topics