Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL : remove last comma in string

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?

like image 742
Justin Avatar asked Nov 29 '22 07:11

Justin


2 Answers

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

like image 39
Felix Pamittan Avatar answered Dec 19 '22 23:12

Felix Pamittan


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>', '')
like image 122
Yair Maron Avatar answered Dec 19 '22 21:12

Yair Maron