SQL : remove last comma in string
Asked Answered
S

7

10

I have a text memo field in SQL table that I need to remove the last character in the field if it's a comma.

So, for example, if I have these rows, I need to remove the commas from rows 2 and 4.

  INETSHORTD
1  94
2  85,
3  94, 92
4  89, 99, 32,

The output would be:

  INETSHORTD
  94
  85
  94, 92
  89, 99, 32

Any ideas?

Sutherland answered 15/7, 2015 at 2:40 Comment(1)
can you post the out put ??Electrolytic
P
8

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

Palladian answered 15/7, 2015 at 2:54 Comment(0)
P
16

Here's a more elegant / readable way:

SET @string = REPLACE(@string + '<END>', ',<END>', '')

if you can't be sure if last comma appear in string, use this:

SET @string = REPLACE(REPLACE(@string + '<END>', ',<END>', ''), '<END>', '')
Pediform answered 16/4, 2020 at 9:6 Comment(1)
Not elegant or readable IMO. What is <END> and why do we repeat it 3 times?Glutelin
P
8

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

Palladian answered 15/7, 2015 at 2:54 Comment(0)
K
8

Not sure if the syntax was available in 2015 but I would think this is a more elegant solution.

SELECT TRIM(',' from INETSHORTD) FROM TABLE
Kenji answered 8/2, 2021 at 6:44 Comment(7)
I prefer this to SELECT REVERSE( STUFF( REVERSE(LTRIM(RTRIM(INETSHORTD))), 1, CASE WHEN SUBSTRING((REVERSE(LTRIM(RTRIM(INETSHORTD)))), 1, 1) = ',' THEN 1 ELSE 0 END, '' ) ) FROM tblApiece
It removed every comma from my string (also not great that you can't use it on variables? - I used the Case option by @sachuGlutelin
@Glutelin Did you use it as per the documentation? It's very hard to make sense of a comment like that without proper context.Kenji
I think what I would have needed was SELECT TRIM(TRAILING ',' FROM @MyVar) As Result - I havent tested it, just going off the documentation you kindly provided, that's how you stop it getting rid of every comma instead of just the last. ThanksGlutelin
best answer, modern TSQL will support more like SELECT TRIM(BOTH ', ' FROM stringvalue ) AS Result; This can remove all characters like ',' and ', ' removing all leading and trailing spaces and commas. Perhaps, you update the answer? learn.microsoft.com/en-us/sql/t-sql/functions/…Angulate
@WalterVerhoeven: The question was "...I need to remove the last character in the field if it's a comma.". So there is no need to remove spaces, and no need to add BOTH, TRAILING would do better, because of the last but this will remove all TRAILING characters, not just 1 last character.Ampere
@Luuk, then TRIM(TRAILING ',' from [columnName]) is your friendAngulate
S
7

Use case statement if the particular string is ending with , take the substring with LEFT function and lenght - 1

Select
    CASE
        WHEN INETSHORTD LIKE '%,'  THEN LEFT(INETSHORTD, LEN(INETSHORTD)-1)
        ELSE INETSHORTD
    END
From yourtable

example fiddle

St answered 15/7, 2015 at 2:49 Comment(2)
thanks, but the SHORTD column is a "text, not null" field, so having trouble with LEN: Argument data type text is invalid for argument 1 of len function.Sutherland
this is actually the best answer for normal people who are using varchar etc. ;)Glutelin
P
1

Using CHARINDEX (https://msdn.microsoft.com/en-us/library/ms186323.aspx) AND LEN (https://msdn.microsoft.com/en-us/library/ms190329.aspx) you should be able to do it like this:

SELECT IIF( CHARINDEX( ',', tmp.SHORTD, LEN( tmp.SHORTD ) ) > 0
          , LEFT( tmp.SHORTD, LEN( tmp.SHORTD ) - 1 )
          , tmp.SHORTD )
  FROM tmp

This SQL Fiddle shows it at work: http://sqlfiddle.com/#!3/a99c8/7.

Plateau answered 15/7, 2015 at 2:51 Comment(2)
thanks, but the SHORTD column is a "text, not null" field, so having trouble with LEN: Argument data type text is invalid for argument 1 of len function.Sutherland
Only because I wanted to prove to myself that my idea could work, you could use SUBSTRING (msdn.microsoft.com/en-us/library/ms187748.aspx) and DATALENGTH (msdn.microsoft.com/en-us/library/ms173486.aspx) to get the results: sqlfiddle.com/#!3/39504e/5Plateau
E
0
declare @t table (id varchar(20)) 
insert into @t(id)values ('94,'),('85,'),('94, 92'),('89, 99, 32,')

SELECT  REVERSE(SUBSTRING(  REVERSE(id),    PATINDEX('%[A-Za-z0-9]%',REVERSE(id)),
    LEN(id) - (PATINDEX('%[A-Za-z0-9]%',REVERSE(id)) - 1)   ) )     
FROM    @t
Electrolytic answered 15/7, 2015 at 2:48 Comment(0)
A
0

If you have a modern version of Microsoft SQL Server, then you can keep it simple and use the build-in trim method.

TRIM ( [ LEADING | TRAILING | BOTH ] [characters FROM ] string )

Consider this code:

declare @result nvarchar(50) =N'   hello world!, ';
print len(@result)
set @Result= TRIM(BOTH ', ' from @Result) 
print @result
print len(@result)

the answer will be:

16 
hello world! 
12

To make it a little clearer, you can look at this sample, which demonstrates the removal of the comma, the white space, and the exclamation mark.

declare @result nvarchar(50) =N'   hello world!, ';
print len(@result)
set @Result= TRIM(BOTH ', !' from @Result) 
print @result
print len(@result)

the answer is now:

16
hello world
11

You can see each character in the [character] parameter is removed.

I used a variable in the demo but it works in columns just as well.

Angulate answered 13/7 at 9:36 Comment(2)
The question was "...I need to remove the last character in the field if it's a comma." it does not say last characterSAmpere
@Luuk, feel free not to use it. There are possible other stackoverflow user that would find this helpfulAngulate

© 2022 - 2024 — McMap. All rights reserved.