Post reply

Remove first comma from stuff

  • September 19, 2019 at 5:15 pm

    #3681512

    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 years, 7 months ago by  Papil.

  • September 19, 2019 at 6:20 pm

    #3681528

    Can't you use RIGHT or SUBSTRING ?

    --Vadim R.

  • September 19, 2019 at 6:50 pm

    #3681547

    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

  • September 19, 2019 at 6:52 pm

    #3681549

    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

  • September 23, 2019 at 9:22 pm

    #3682730

    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. 

  • September 23, 2019 at 10:13 pm

    #3682746

    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.

    Change is inevitable... Change for the better is not.

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

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

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