CAST/CONVERT nvarchar to float for sorting, but only if a number

Hi,

I have a SQL Server 2000 database with an nvarchar column containing a mixture of strings, e.g. "Introduction", "Appendix A" and section numbers, e.g. "2.3", "9.1" or "10.2",

I need to be able to sort this column in my SELECT statement but because the type is nvarchar, the numbers are being sorted as strings, so "10" is coming before "1".

I am looking for a way to be able to sort these values so that text values come at either the beginning or end of the list (there are only a few so it doesn't matter how they're ordered) and numbers are listed in numerical order.

Here's an example of what I'm trying:

SELECT Section AS 'SectionAsNVarChar'
ORDER BY CAST(ISNULL(Section, '0') AS float) ASC

The idea of the above is to list the sections using their string representation, but sort them by their numeric value.

Where I'm stuck, is finding a way of saying "if the value can't be CAST into a float, set it to 0".

Tom
heathwallaceAsked:
Who is Participating?
 
derekkrommCommented:
order by
case ISNUMERIC(selection)
      when 0 then 0
      else cast(selection as float)
end desc
0
 
dbbishopCommented:
ORDER BY COALESCE(CAST(Section AS float), 0) ASC
0
 
heathwallaceAuthor Commented:
I've heard ISNUMERIC isn't very reliable, is it likely to be a problem in this case?
0
 
derekkrommCommented:
i never heard it was unreliable. always worked for me
0
 
heathwallaceAuthor Commented:
Thanks, it seems to be working fine.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.