Home Forums SQL Server 2012 SQL Server 2012 - T-SQL Find integer at end of a string. There *must* be a better solution ? RE: Find integer at end of a string. There *must* be a better solution ?

  • July 27, 2013 at 12:20 pm

    #1636414

    The use of IsNumeric in onne of the solutions serves as a reminder that there are a whole lot of things that look like numbers to SQLServer. Here's some updated test data to see what I mean...

    with tmp as

    (

    SELECT x = 'some text-1'

    UNION ALL SELECT x = 'some text-123'

    UNION ALL SELECT x = 'some text-123.4'

    UNION ALL SELECT x = 'some text 123'

    UNION ALL SELECT x = 'some text-'

    UNION ALL SELECT x = 'some text- ' --Added this for possible CHAR values

    UNION ALL SELECT x = 'some text- 123 ' --Added "stray bullet"

    UNION ALL SELECT x = 'some text-123 456' --Added "stray bullet"

    UNION ALL SELECT x = 'some text-123d4' --Added "stray bullet"

    UNION ALL SELECT x = 'some text-123e4' --Added "stray bullet"

    UNION ALL SELECT x = 'some text-$1,234' --Added "stray bullet"

    UNION ALL SELECT x = 'some text-1,234' --Added "stray bullet"

    UNION ALL SELECT x = 'some text-0x1234,' --Added "stray bullet"

    UNION ALL SELECT x = 'some text'

    UNION ALL SELECT x = 'some text-xyz'

    )

    ... put your code here ...

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