Order BY in SQL

Question

In my SQL statement, need to parse a title field so I can order by the integers. Basically, just extract the integers into a new field and order on that field.

the data in the title field looks like the following (WRONG):

TT TEST 750-1
TT TEST 80-1
TT TEST 1 UU 25-1
TT TEST 1 UU 420-1
TT TEST 1 UU 55-46
TT TEST 1 UU 600-8-10
TT TEST 1 UU 608-20
TT TEST 1 UU 750-1

after order by (CORRECT)

TT TEST 80-1
TT TEST 750-1
TT TEST 1 UU 25-1
TT TEST 1 UU 55-46
TT TEST 1 UU 420-1
TT TEST 1 UU 600-8-10
TT TEST 1 UU 608-20
TT TEST 1 UU 750-1

Thanks.

Answer 1

You could use the PATINDEX function:

SELECT 
    CAST(SubString(REPLACE(Title,'-',''),
    PATINDEX('%[0-9]%',REPLACE(Title,'-','')),
    Len(REPLACE(Title,'-',''))) 
    AS INT)
AS [Title Number],*
FROM [Your table-name here] 
ORDER BY [Title Number]

Edit: Will work with hyphen now.

Note: Will not work if the Non-numeric part of the title has numbers or has a hyphen ('-').