Post reply

SQL WHERE and SELECT portion of character string after defined text

  • June 1, 2016 at 7:41 pm

    #400227

    Hi All,

    I am trying to setup a select query + where statement with parameters that KeyID equals numeric string after the first 83 digits.

    A barcode will be scanned such as 00008311592 or 00000838359

    The select statement should match the KeyID, in the selected table, 11592 or 8359 in the examples above.

    Thank you in advance for your assistance!

    -Chetta

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

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