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 2:56 pm

    #1636421

    Thorkil Johansen (7/27/2013)To sharpen a little: "The integer to the right of the last hyphen in the string"

    In such case...

    with tmp as (

    select x = 'some text-1'

    union select x = 'some text-123'

    union select x = 'some text-123.4'

    union select x = 'some text 123'

    union select x = 'some text-'

    union select x = 'some text'

    union select x = 'some text-xyz'

    union select x = 'some text-2.2-44'

    )

    select * ,

    coalesce( TRY_CONVERT(int,

    nullif(SUBSTRING(x, len(x) - nullif(CHARINDEX('-', reverse(x)), 0) + 2, LEN(x)),

    '')),

    -1)

    from tmp

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]