how to sort the alphanumeric values in sql

Hi there,

Would it make sense if you split the list of data into two? One list containing only numbers, the other containing the rest of it. Sort these 2 lists separately, and merge them (append the other list to the end of the numeric list)

I suppose this isn't hard to do, but you will need to write a stored procedure with a temporary table or two.

Hope this helps :) Regards

Please check the following SQL code. I hope it will now resolve ur problem....

CREATE TABLe #T -- original
(
T VARCHAR(10)
)

CREATE TABLe #TSort --sorted
(
ID INT IDENTITY,
T VARCHAR(10)
)

INSERT INTO #T
SELECT '0.2a' UNION ALL
SELECT '1' UNION ALL
SELECT '001' UNION ALL
SELECT '1a' UNION ALL
SELECT '08' UNION ALL
SELECT '8.003' UNION ALL
SELECT 'a' UNION ALL
SELECT 'a.2' UNION ALL
SELECT 'z' UNION ALL
SELECT '10' UNION ALL
SELECT '11' UNION ALL
SELECT '2' UNION ALL
SELECT '3' UNION ALL
SELECT '31' UNION ALL
SELECT '4' UNION ALL
SELECT '46' UNION ALL
SELECT '21'


INSERT INTO #TSort
SELECT T
FROM #T
WHERE (ISNUMERIC(LEFT(T, LEN(T) - 1)) = 1 AND ISNUMERIC(T) = 0) OR ISNUMERIC(T) = 1
ORDER BY CASE
WHEN (ISNUMERIC(LEFT(T, LEN(T) - 1)) = 1 AND ISNUMERIC(T) = 0) THEN CONVERT(FLOAT,LEFT(T, LEN(T) - 1))
WHEN ISNUMERIC(T) = 1 THEN CONVERT(FLOAT,T) END

INSERT INTO #TSort
SELECT T FROM #T WHERE ISNUMERIC(T) = 0 AND ISNUMERIC(LEFT(T, LEN(T) - 1)) = 0
ORDER BY T

SELECT T FROM #TSort ORDER BY ID

DROP TABLE #T
DROP TABLE #TSort

Thanks

Your query should be :

Select * from table_name order by column_name

ascending is by default
for descending :

Select * from table_name order by column_name desc