billy_111 — 2011-03-03T11:38:37-05:00 — #1
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?
r937 — 2011-03-03T12:13:29-05:00 — #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
r937 — 2011-03-03T12:14:56-05:00 — #3
p.s. i nominate the above post for :award: Elegant Solution O' Da Month
billy_111 — 2011-03-03T12:20:44-05:00 — #4
Thanks for your suggestion
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?
r937 — 2011-03-03T12:24:31-05:00 — #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
billy_111 — 2011-03-13T15:34:53-04:00 — #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
r937 — 2011-03-13T15:48:25-04:00 — #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"
btw, i know what the error is already, the crystal ball was not needed
billy_111 — 2011-03-14T04:04:26-04:00 — #8
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
The syntax error is:
'IFNULL' is not a recognized built-in function name.
billy_111 — 2011-03-14T04:23:25-04:00 — #9
by the way i am using SQL Server 2008 R2
r937 — 2011-03-14T04:41:16-04:00 — #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)
billy_111 — 2011-03-14T04:57:57-04:00 — #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
r937 — 2011-03-14T05:05:47-04:00 — #12
what do you initialize the string with?
obviously there is some kind of looping going on here, right?
billy_111 — 2011-03-14T05:10:45-04:00 — #13
I have it in a CROSS APPLY like so:
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?
r937 — 2011-03-14T05:14:51-04:00 — #14
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
billy_111 — 2011-03-14T05:18:41-04:00 — #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
sqls — 2011-03-16T16:41:40-04:00 — #16
If you always have comma at the end of the string, you could try this:
SUBSTRING (str, 1, len(str)-1)