Ordering a list of fractions and mixed numbers

Question

I am looking for a way that would allow me to have a column that I would simply manually input a number and then the list would sort itself based off this list, this would be using c# as the language for the listing and it's ms SQL

I don't have much information, but if anyone wants to know anything else feel free to ask and I will try and answer to my best ability.

They are currently stored as strings.

The thing that causes it to get thrown out is because some of the lists contains ranges but some contain fractions, both which are displayed the same I.E 1/2 can mean 1-2 or 1/2(half)

All of the SQL connection is done using NHibernate.

The reason for not simply sorting it normally, is that the list is ordered using fractions currently and the list works fine, however when it gets over 1, it seems to break and throws all of them to the bottom of the list I.E:

Example of how I would like this to work:

I would have a column in my database named "DisplayOrder" or something along those lines.

Database row says "1", this would be the first item in a list to appear. Database row says "8", this would be the 8th item in the list to appear.

Answer 1

I've put together a quick function to parse your fractions and convert them to floats, which can then be used for comparison and sorting. You may need more in the "sanitation" step, possibly normalizing whitespace, removing other characters, etc., and may have to check the divisor for zero (only you know your data).

create function dbo.FractionToFloat(@fraction varchar(100))
returns float
as
begin
    declare @input varchar(100)
        , @output float
        , @whole int
        , @dividend int
        , @divisor int

    -- Sanitize input
    select @input = ltrim(rtrim(replace(replace(@fraction, '''', ''), '"', '')))

    select @whole = cast(case
        when charindex('/', @input) = 0 then @input
        when charindex(' ', @input) = 0 then '0'
        else left(@input, charindex(' ', @input) - 1)
        end as int)

    select @dividend = cast(case
        when charindex('/', @input) = 0 then '0'
        when charindex(' ', @input) = 0 then left(@input, charindex('/', @input) - 1)
        else substring(@input, charindex(' ', @input) + 1, charindex('/', @input) - charindex(' ', @input) - 1)
        end as int)

    select @divisor = cast(case
        when charindex('/', @input) = 0 then '1'
        else right(@input, charindex('/', reverse(@input)) - 1)
        end as int)

    select @output = cast(@whole as float) + (cast(@dividend as float) / cast(@divisor as float))

    return @output
end

This way, you can simply order by the function's output like so:

select *
from MyTable
order by dbo.FractionToFloat(MyFractionColumn)

I wouldn't normally suggest rolling your own parser that you have to maintain as the data changes, but this seems simple enough on the surface, and probably better than manually maintaining an ordinal column. Also, if you had to compare various units (feet to inches, minutes to seconds), then this gets more complicated. I'm removing anything that looks like a unit in your demo data.