How to properly sort alphanumeric in SQL Server 2000

Question

MS SQL Server 2000

I have a column in Table A called Name. I wish to sort the Name field. Many but not all of the records for Name start will KL and are followed by a number (KL 1234, KL 2, KL 323, etc).

Table A

Name

Able
Bravo
KL 2
KL 323
KL 1234
Zebra

If I use

Select Name from A 
Order by Name

I get

Able
Bravo
KL 1234
KL 2
KL 323
Zebra

I want

Able
Bravo
KL 2
KL 323
KL 1234
Zebra

If they all started with KL I could use

Select Name from A
Order by cast(replace(name, 'KL', '') as big int)

but this generates an "unble to cast name as big int" error for values that do not start with KL

Thanks for any help.

Answer 1

Try this:

Order By 
    Case When Left(name, 2) = 'KL' 
        Then 'KL' + Replace(Str(Cast(replace(name, 'KL', '') as BigInt), 12), ' ', '0')
        Else name End

Answer 2

ORDER BY 
    CASE WHEN CHARINDEX(' ', name)=0 THEN name 
        ELSE LEFT(name, CHARINDEX(' ', name)) END,
    CASE WHEN CHARINDEX(' ', name)=0 THEN 0
        ELSE CONVERT(BIGINT, 
            SUBSTRING(name, CHARINDEX(' ', name)+1, LEN(name))) END

updated wrong closing ) after comment