Remove first comma from stuff

  • Papil

    SSCommitted

    Points: 1948

    Hi,

    I am doing STUFF in my query which is concatenating the blank data too and data looks like below. Sometimes there is space+comma and sometimes only comma in the beginning which i need to remove.

    Could you help with same. I tried case statement but that makes the query really bigger.

    ,666,656

    ,54545

    Thanks

     

    • This topic was modified 4 weeks, 1 day ago by  Papil.
  • rVadim

    Hall of Fame

    Points: 3911

    Can't you use RIGHT or SUBSTRING ?

    --Vadim R.

  • drew.allen

    SSC Guru

    Points: 76594

    Papil wrote:

    Hi,

    I am doing STUFF in my query which is concatenating the blank data too and data looks like below. Sometimes there is space+comma and sometimes only comma in the beginning which i need to remove.

    Could you help with same. I tried case statement but that makes the query really bigger.

    ,666,656

    ,54545

    Thanks

    It sounds like you are using code that you don't fully understand.  STUFF does not concatenate text.  It replaces a segment of a string in the specified position with another string.  I'm assuming that you are using the STUFF/XML combo, in which case it is the XML that is concatenating the text.

    Here is the syntax:  STUFF(<string expression>, <start position>, <length>,<replacement text>).  An example would be STUFF(',345,678', 1, 1, '') which would replace the section starting in position 1 and length 1, i.e., , and replacing it with an empty string ''.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • drew.allen

    SSC Guru

    Points: 76594

    rVadim wrote:

    Can't you use RIGHT or SUBSTRING ?

    You can use RIGHT() or SUBSTRING(), but both of those tend to be more complex than STUFF().

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • jcelko212 32090

    SSCrazy Eights

    Points: 8854

    Why are you doing display formatting in a database? That's supposed to be done and presentation layer. The leading comma is an old trick we used to use back in the punchcard days. It makes it easy to rearrange the deck of cards or to reuse them. Of course, it did add about 8% more time to debugging (the University of Maryland had some research on this back in the 1970s). If you remember  1NF (First Normal Form), the columns in the result set should all be scalar variables,  not comma-separated list.

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • Jeff Moden

    SSC Guru

    Points: 994667

    Papil wrote:

    Hi,

    I am doing STUFF in my query which is concatenating the blank data too and data looks like below. Sometimes there is space+comma and sometimes only comma in the beginning which i need to remove.

    Could you help with same. I tried case statement but that makes the query really bigger.

    ,666,656

    ,54545

    Thanks

    Please post the entire code.  We're only guessing without seeing the actual code.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply