How to remove the last comma

Databases
1

Hey,

I am returning a table row like so:


ISNULL(I.Name, '') + ', '

This returns the data like so:

One, Two, Three,

But i’m not so sure how to remove the last comma, i have looked at the LEFT and CHARINDEX operators but can’t quite figure out the syntax.

Can anyone help?

Thanks,

Kind regards,

2

why take off what you don’t need to add on?

let me explain more clearly…

every time you add a term to the list, you’re adding a comma-space after it, and the thing is, you don’t know when the list is finished, so lopping off the trailing comma is a second operation, not to mention tricky if you have to ask for help on it

my idea is, the other end of the list, the front end, you definitely do know when you’ve reached it, and that’s right at the beginning of your concatenation process, right?

okay, so change this –

ISNULL(I.Name, '') + ', '

to this –

ISNULL(I.Name+ ', ' , '')

ah, the beauty of it :smiley: :smiley:

3

p.s. i nominate the above post for :award: Elegant Solution O’ Da Month

:cool:

4

Thanks for your suggestion :slight_smile:

But that didn’t seem to work. Ic an see what you mean, but wouldn’t that return a comma after every instance of the row that is returned?

5

see, this is what happens when you think you are pretty good, you invariably screw up when you say anything about it

i was wrong, it should of course be

IFNULL(', '+name,name)

:blush:

6

Sorry i didn’t get a chance to get back to that piece of SQL. Had to work on something else.

Ok that also didn’t work.

I tried the IFNULL and got a syntax error.

Is there not a way to do it by “LEFT”, “CHARINDEX” AND “SUBSTRING”?

Let’s say i have 3 values comma separated i would want them to appear like so:

One, two, three

7

come on, man, you know the drill here…

you say “i got a syntax error”

then i say “my crystal ball is down at the moment, could you show me the error please”

:slight_smile:

btw, i know what the error is already, the crystal ball was not needed

8

Haha :smiley:

Sorry i was trying to do a Remote Desktop to my PC at work but couldn’t get connection thus could not show you the error!

Anyway, it’s Monday again :frowning:

The syntax error is:

‘IFNULL’ is not a recognized built-in function name.

9

by the way i am using SQL Server 2008 R2 :slight_smile:

10

if you haven’t figured it out, i made yet another mistake, a typo

instead of IFNULL(', '+name,name) it should have been ISNULL(', '+name,name)

11

I did think it was a typo when i got that message but when i try your suggestion i get this:

, One, Two, Three

So now there is an extra comma at the beginning :stuck_out_tongue:

12

what do you initialize the string with?

obviously there is some kind of looping going on here, right?

13

Yes,

I have it in a CROSS APPLY like so:


CROSS APPLY(
	    SELECT ISNULL(', '+I.Notes,I.Notes)
	    FROM Invoice I
	    WHERE I.SourceID = B.ID AND I.Notes LIKE '%Discount%'
	    FOR XML PATH('')
) D (DiscountsUsed)

I couldn’t do this insude a subquery as it is returning more than one value so i did it outside and then i just refer to it as “DiscountsUsed”…

Am i doing something wrong? :confused:

14

probably not

however, i’m not up to speed on CROSS APPLY and XML PATH so i think perhaps my technique isn’t going to help you

15

Ah i see. It’s ok i’m going to try looking at a different method, CHARINDEX or SUBSTRING and LEFT. If i work out a solution i will post the answer here :slight_smile:

16

If you always have comma at the end of the string, you could try this:


SUBSTRING (str, 1, len(str)-1)