SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Wizard billy_111's Avatar
    Join Date
    Jul 2009
    Posts
    1,683
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to remove the last comma

    Hey,

    I am returning a table row like so:

    PHP Code:
    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,
    Software Engineer ASP, ASP.NET, VB, PHP

    Kind regards
    Billy

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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 --
    Code:
    ISNULL(I.Name, '') + ', '
    to this --
    Code:
    ISNULL(I.Name+ ', ' , '')
    ah, the beauty of it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    p.s. i nominate the above post for Elegant Solution O' Da Month

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Wizard billy_111's Avatar
    Join Date
    Jul 2009
    Posts
    1,683
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?
    Software Engineer ASP, ASP.NET, VB, PHP

    Kind regards
    Billy

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    Code:
    IFNULL(', '+name,name)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Wizard billy_111's Avatar
    Join Date
    Jul 2009
    Posts
    1,683
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Software Engineer ASP, ASP.NET, VB, PHP

    Kind regards
    Billy

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by billy_111 View Post
    I tried the IFNULL and got a syntax error.
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Wizard billy_111's Avatar
    Join Date
    Jul 2009
    Posts
    1,683
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Haha

    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.
    Software Engineer ASP, ASP.NET, VB, PHP

    Kind regards
    Billy

  9. #9
    SitePoint Wizard billy_111's Avatar
    Join Date
    Jul 2009
    Posts
    1,683
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    by the way i am using SQL Server 2008 R2
    Software Engineer ASP, ASP.NET, VB, PHP

    Kind regards
    Billy

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Wizard billy_111's Avatar
    Join Date
    Jul 2009
    Posts
    1,683
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Software Engineer ASP, ASP.NET, VB, PHP

    Kind regards
    Billy

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by billy_111 View Post
    So now there is an extra comma at the beginning
    what do you initialize the string with?

    obviously there is some kind of looping going on here, right?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Wizard billy_111's Avatar
    Join Date
    Jul 2009
    Posts
    1,683
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes,

    I have it in a CROSS APPLY like so:

    Code:
    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?
    Software Engineer ASP, ASP.NET, VB, PHP

    Kind regards
    Billy

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by billy_111 View Post
    Am i doing something wrong?
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Wizard billy_111's Avatar
    Join Date
    Jul 2009
    Posts
    1,683
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Software Engineer ASP, ASP.NET, VB, PHP

    Kind regards
    Billy

  16. #16
    SitePoint Member
    Join Date
    Mar 2011
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you always have comma at the end of the string, you could try this:
    PHP Code:
    SUBSTRING (str1len(str)-1


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •