Sql Server命令按nvarchar字段[重复]

时间:2021-11-25 15:42:11

This question already has an answer here:

这个问题在这里已有答案:

I'm trying to sort out this data by using Order By but the Order by is not working properly as I wanted

我正在尝试使用Order By来排序这些数据,但Order by无法正常工作

Here's my code:

这是我的代码:

SELECT (SELECT LEFT(SUBSTRING(description, PATINDEX('%[0-9.-]%', description), 8000),
       PATINDEX('%[^0-9.-]%', SUBSTRING(description, PATINDEX('%[0-9.-]%', description), 8000) + 'X') -1)) as x      
FROM dbo.fn_BMS_PPA() 
WHERE ppayear = 2017 and actualrootppaid = 112905 
and actualmotherppaid != 0 and withchildppa = 0
ORDER BY x

Output is:

输出是:

1.1.1
1.1.10
1.1.11
1.1.2
1.1.3
.....

What I want is :

我想要的是:

1.1.1
1.1.2
1.1.3
1.1.10
1.1.11
.....

Please take note that sometimes it will be 1.1 or 1.1.1.1

请注意,有时它将是1.1或1.1.1.1

1 个解决方案

#1


0  

This article should help you http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=130084 Basically you cast before you sort.

本文应该可以帮助您http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=130084基本上您在排序之前进行了投射。

#1


0  

This article should help you http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=130084 Basically you cast before you sort.

本文应该可以帮助您http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=130084基本上您在排序之前进行了投射。