Database number order problem

[song2siren]

Hello

I've created a some asp pages which display results from a database of legal journals by citation order. I use ORDERStmt = "ORDER BY citation".

However, this means citations with two digits on the end such as [2002] CFLQ 25 come after those with three digits such as [2002] CFLQ 237. I just need these citations to appear in the correct number order. Hopefully this is fairly simple.

Any help would be much appreciated!

 

[mitchadams]

try something like this...

Order by CONVERT(int,REPLACE(citation, '[2002] CFLQ ',''))

This will remove the [2002] CFLQ from the beginning and turn the number into an int which can be ordered as such.

Mitch

Bournemouth UK

 

[MaffewW]

or, presuming the value [2002] CFLQ can change (i guess 2002 is the year) and presuming the order by is a 2 or 3 digit number (add another step if theres 4 or more numbers)this should work


order by convert(int,(case when isnumeric(right(phonenumber,3)) = 1 then right(phonenumber,3)
when isnumeric(right(phonenumber,2)) = 1 then right(phonenumber,2)
end))

Matt

Brighton, UK