Post reply

SQL WHERE and SELECT portion of character string after defined text

  • June 1, 2016 at 8:19 pm

    #1882696

    The comments in the code explain it. Remember that you don't have to calculate the length of the substring for things like this. SQL Server will do that for you.

    SELECT tt.SomeString --just for visual verification during testing

    ,AfterFirst83 = SUBSTRING(tt.SomeString,NULLIF(CHARINDEX('83',tt.SomeString),0)+2,8000)

    FROM ( --=== Simulate a small test table

    SELECT '00008311592' UNION ALL --11592 based on position of only "83"

    SELECT '00000838359' UNION ALL --8359 based on position of first "83"

    SELECT '00008211592' UNION ALL --NULL (indicates no "83")

    SELECT '83' UNION ALL --Blank (indicates an "83" with nothing following)

    SELECT '123483' --Blank (indicates an "83" with nothing following)

    ) tt (SomeString)

    ;

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

  • June 1, 2016 at 8:25 pm

    #1882697

    This is great, thank you!

  • June 1, 2016 at 8:46 pm

    #1882704

    You bet. Thank you for the feedback.

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

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