如何在SQL中对字母数字值进行排序 [英] how to sort the alphanumeric values in sql

问题描述

大家好，

我处于关键位置，任何人都可以在这种情况下为我提供帮助.实际我的问题是在我的表中有一个nvarchar类型的数据，其数据如

1
0.2a
1a
a.2
z
a
001
08
008.003


我需要输出为:
0.2a
1
001
1a
08
8.003
a
a.2
z

在此先感谢
Seshu

Hi folks,

I am in critical position any one can help me from this condition . Actual my problem is in my table there is a nvarchar type data which having data like

1
0.2a
1a
a.2
z
a
001
08
008.003


I need output as:
0.2a
1
001
1a
08
8.003
a
a.2
z

Thanks in advance
Seshu

推荐答案

SELECT id FROM sortaln ORDER BY
CASE WHEN ISNUMERIC(id) = 1 THEN RIGHT(REPLICATE('0',51) + id, 50)
     WHEN ISNUMERIC(id) = 0 THEN LEFT(id + REPLICATE('',51), 50)
     ELSE id
END

试试这个，希望对您有帮助

try this i hope it helps you

您好，

如果将数据列表一分为二，这有意义吗?一个列表仅包含数字，另一个包含其他列表.分别对这两个列表进行排序，然后合并它们(将另一个列表附加到数字列表的末尾)

我想这并不难，但是您将需要编写一个或两个临时表的存储过程.

希望对您有所帮助:)问候

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

请检查以下SQL代码.我希望它现在能解决您的问题....

创建标签#T-原始
(
T VARCHAR(10)
)

创建标签#TSort --sorted
(
ID INT IDENTITY，
T VARCHAR(10)
)

插入#T
选择``0.2a''UNION ALL
选择``1''UNION ALL
选择''001''UNION ALL
选择``1a''UNION ALL
选择''08''UNION ALL
选择''8.003''UNION ALL
选择''a''UNION ALL
选择''a.2''UNION ALL
选择''z''UNION ALL
选择``10''UNION ALL
选择``11''UNION ALL
选择``2''UNION ALL
选择``3''UNION ALL
选择''31''UNION ALL
选择``4''UNION ALL
选择''46''UNION ALL
选择''21''


插入#TSort
SELECT T
从#T
在哪里(ISNUMERIC(LEFT(T，LEN(T)-1))= 1 AND ISNUMERIC(T)= 0)或ISNUMERIC(T)= 1
按案例订购
当(ISNUMERIC(LEFT(T，LEN(T)-1))= 1并且ISNUMERIC(T)= 0)然后转换(FLOAT，LEFT(T，LEN(T)-1))
当ISNUMERIC(T)= 1然后转换为(FLOAT，T)END

插入#TSort
从#T中选择T，其中ISNUMERIC(T)= 0并且ISNUMERIC(LEFT(T，LEN(T)-1))= 0
按T排序

从#TSort按ID排序
中选择T
拖放表#T
删除表#TSort

谢谢

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

这篇关于如何在SQL中对字母数字值进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！