SQL query: how to convert varchar to signed integer for SQL Server 2000?

Question

I got a column called panel no

panel no
--------
1
10
1A
2A
6
...

I would like to have the order like

panel no
--------
1
1A
2A
6
10

I try "order by cast(panel no as signed)" and it works for mysql but not in SQL Server 2000.

what can I do?

Please do not use tags that do not apply to your question (i.e. mysql) — John Conde, Aug 11 '14 at 16:08
`1A` is not an integer so you can't just cast that to an int. What is the exact code you are trying in SQL Server? — Taryn, Aug 11 '14 at 16:11
The accepted solution of http://stackoverflow.com/questions/10443462/how-to-get-the-numeric-part-from-a-string-using-t-sql should work with SQL Server 2000 too. CAST the result. — VMai, Aug 11 '14 at 16:25
I send the query: "SELECT * FROM tableA WHERE columnA = 'abc' ORDER BY CAST('panel no' AS SIGNED) "signed" is pointed out as an error — user3930299, Aug 12 '14 at 13:26

score 0 · Accepted Answer · edited May 23 '17 at 12:20

SELECT 
    * 
FROM 
    TableA
WHERE
    columnA = 'abc'
ORDER BY
    CAST(LEFT([panel no], patindex('%[^0-9]%', [panel no] + '.') - 1) AS INT);

Please note that the column name panel no contains a blank, so it's got to be quoted according the rules of T-SQL.

See this demo.
This solution is compatible with SQL Server 2000.

1 Answers1