Sorting in T-SQL

I have some strings like

"999"
"555"
"7777"
"CC44"
"AAAA"
"BBBB"

How can I sort so that the output will be

"999"
"7777"
"555"
"AAAA"
"BB开发者_运维技巧BB"
"CC44"

The rule is : Based on the string's numeric value in ascending order

I have included the script too

declare @tbl  Table(
    data VARCHAR(MAX)   
)

INSERT INTO @tbl (data)

SELECT '999' UNION ALL
SELECT '555' UNION ALL
SELECT '7777' UNION ALL
SELECT 'CC44' UNION ALL
SELECT 'AAAA' UNION ALL
SELECT 'BBBB' 

That isn't a natural sort order, so you might need to add another column to perform the sorting. If that isn't possible, you will need to split the data set, sort each half, and rejoin them.

SELECT data FROM tbl WHERE columnname > "0" AND columnname < "9999" ORDER BY columnname DESC
UNION ALL
SELECT data FROM tbl WHERE columnname < "9999"
ORDER BY columnname ASC

Not tested but:

select col where IsNumeric(col)=1

order by IsNumeric(col) asc, col