I would create a table to hold the numeric and string equivalents, then
join that into your queries when you need it. You can use a Numbers
table to help create this conversion
(
http://www.bizdatasolutions.com/tsql/tblnumbers.asp)
CREATE TABLE foo (fraction VARCHAR(10) PRIMARY KEY, value NUMERIC(10,2)
NOT NULL UNIQUE)
INSERT INTO foo (fraction, value)
SELECT CAST(N.num AS VARCHAR(10))+
CASE value WHEN 0 THEN '' ELSE '-' END +
fraction, N.num+value
FROM
(SELECT '',0 UNION ALL
SELECT '1/4',0.25 UNION ALL
SELECT '1/2',0.50 UNION ALL
SELECT '3/4',0.75 /* ... etc */
) AS F(fraction,value),
Numbers AS N
WHERE N.num<10
I assume you need to store the strings in this way in order to join them
with some other, similarly formatted data. I would still consider
storing them as numerics and then using the above as a joining table in
queries. If that doesn't suit then here is the ORDER BY:
SELECT col
FROM YourTable AS T1
JOIN Foo AS T2
ON T1.col = T2.fraction
ORDER BY T2.value
Hope this helps.
--
David Portas
SQL Server MVP
--
*** Sent via Developersdex
http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!