sorting string

hi!!!

would appreciate if somebody could let me know the query/algoritham to
sort the following values in the column either in asc or desc

the values in the column originally

1
1-1/2
1-1/4
1/2
10
12
14
16
18
2
2-1/2
20
3
3/4
4
5
6
8

the sorted result should be as follows

1/2
3/4
1
1-1/4
1-1/2
2
2-1/2
3
4
5
6
8
10
12
14
16
18
20

TIA, have a great day

regards
bala

re: sorting string

If these are supposed to be numeric values (1, 1.5, 1.25, 0.5, etc) then why
not store them as such? Make the column a NUMERIC, then format them as whole
numbers and fractions in the client application when you display or print
them. That way you'll find sorting and comparison much easier.

--
David Portas
SQL Server MVP
--

re: sorting string

hi david

well the requirement is such that it needs to be stored as string but
sorted as numeric. the sorting has to been done in the backend.

if it can stored as numeric life would have been simple since order by
would do the trick.

with the above mentioned conditions would appreciate if someone be
guiding light to attain the solution.

thanx
bala

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

re: sorting string

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!

re: sorting string

hi david

thanx a ton, have a great weekend

regards
bala



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!