Post reply

removal of commas at end

  • February 14, 2011 at 4:12 am

    #98063

    Hi

    if I have a string like this 1234,345-5678,122-789,,,,,

    then what is the best way to remove the last commas. my output should be like

    1234,345-5678,122-789

    since this string is part of select clause of my statement, I dont want any loops for a solution.Basically string functions alone should be able to do this....

    Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

  • February 14, 2011 at 4:57 am

    #1286396

    declare @test-2 varchar(max) = '1234,345-5678,122-789,,,,,'

    select REVERSE(LTRIM(RTRIM(RIGHT(REVERSE(@test),LEN(@test) - ((PATINDEX('%[^,]%',REVERSE(@test) )))+ 1))))

    There will be other (and more than likely, better) ways to solve this, bit I've tested this against a variety of values and it appears to do as required. I'm not convinced it is as compact as it could be, but it kind of mutated as I played with it.

    Have a play with it and see if it is of any use.

    BrainDonor.

    Steve Hall
    Linkedin
    Blog Site

  • February 14, 2011 at 5:07 am

    #1286400

    Thanks for trying it. Actually the variable @test-2 you have used, is actually a very long select clause in my case (100 lines to be precise). So every time I repeat @test-2, my code is gettting bigger by 100 lines.

    I just want to keep it short and clean 🙂

    Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

  • February 14, 2011 at 5:24 am

    #1286410

    I'm not sure I understand what you're explaining there.

    If the data you're trying to manipulate is in the Select clause, then all you should need to do is insert the code I used into that (assuming 'TargetColumn' has the data to be altered when selected):

    SELECT fb.OneColumn,

    fb.AnotherColumn,

    REVERSE(LTRIM(RTRIM(RIGHT(REVERSE(fb.TargetColumn),LEN(fb.TargetColumn) - ((PATINDEX('%[^,]%',REVERSE(fb.TargetColumn) )))+ 1)))) AS TargetColumn

    FROM Fubar fb

    If I haven't understood correctly then please post the table definition, example data and expected results.

    BrainDonor.

    Steve Hall
    Linkedin
    Blog Site

  • February 14, 2011 at 5:59 pm

    #1286805

    S_Kumar_S (2/14/2011)

    Thanks for trying it. Actually the variable @test-2 you have used, is actually a very long select clause in my case (100 lines to be precise). So every time I repeat @test-2, my code is gettting bigger by 100 lines.

    I just want to keep it short and clean 🙂

    Put the results in a Temp Table. 😉

    --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 5 posts - 1 through 4 (of 4 total)

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