Return string between 2 characters from the end of the string

  • manderson 20961 (6/28/2016)


    Sorry guys my first post on here. I'll try to be better the next time. Thanks for all your help!

    Understood. Please see the first link under "Helpful Links" in my signature line below for what we're talking about. It can really help a whole lot. Some folks actually get angry when we ask them to do such a thing. We're just trying to help.

    --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)

  • Hope this should work.

    DECLARE @a varchar(20) = 'INV CRT IS15000467 1'

    DECLARE @b-2 varchar(20) = 'INV CSH IS15000442 10'

    select substring(stuff(@a, 1,Charindex(' ',@a, Charindex(' ',@a,0)+1),''),0, Charindex(' ', stuff(@a, 1,Charindex(' ',@a, Charindex(' ',@a,0)+1),'')))

    select substring(stuff(@b, 1,Charindex(' ',@b, Charindex(' ',@b,0)+1),''),0, Charindex(' ', stuff(@b, 1,Charindex(' ',@a, Charindex(' ',@b,0)+1),'')))

  • It should work I think. Please let me know if need some more changes..

    DECLARE @a varchar(20) = 'INV CRT IS15000467 1'

    DECLARE @b-2 varchar(20) = 'INV CSH IS15000442 10'

    select substring(stuff(@a, 1,Charindex(' ',@a, Charindex(' ',@a,0)+1),''),0, Charindex(' ', stuff(@a, 1,Charindex(' ',@a, Charindex(' ',@a,0)+1),'')))

    select substring(stuff(@b, 1,Charindex(' ',@b, Charindex(' ',@b,0)+1),''),0, Charindex(' ', stuff(@b, 1,Charindex(' ',@a, Charindex(' ',@b,0)+1),'')))

  • ammit.it2006 (6/29/2016)


    It should work I think. Please let me know if need some more changes..

    DECLARE @a varchar(20) = 'INV CRT IS15000467 1'

    DECLARE @b-2 varchar(20) = 'INV CSH IS15000442 10'

    select substring(stuff(@a, 1,Charindex(' ',@a, Charindex(' ',@a,0)+1),''),0, Charindex(' ', stuff(@a, 1,Charindex(' ',@a, Charindex(' ',@a,0)+1),'')))

    select substring(stuff(@b, 1,Charindex(' ',@b, Charindex(' ',@b,0)+1),''),0, Charindex(' ', stuff(@b, 1,Charindex(' ',@a, Charindex(' ',@b,0)+1),'')))

    That will work fine for the data you posted. It won't work fine for the data the OP posted, which has a bunch of extra spaces in it that were suppressed by the HTML-nature of this forum. Go back and see my post to Luis to see what I mean.

    --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 4 posts - 16 through 18 (of 18 total)

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