Clever Geek Handbook

How to properly sort alphanumeric data in SQL Server 2000

MS SQL Server 2000

I have a column in table A called Name. I want to sort the Name field. Many, but not all, entries for the start of a name will be KL 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 to

Able
Bravo
KL 2
KL 323
KL 1234
Zebra

If it 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 large int" error for values ​​that do not start with KL

Thanks for any help.

0


source to share


2 answers


Try the following:



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


source 


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 close) after comment

0


source






More articles:


All Articles