如何在 SQL Server 中按数字对 varchar 数据类型列进行排序 [英] How to sort a varchar datatype column by its numbers in SQL Server

问题描述

我在 SQL Server 中有一个包含如下数据的列:

I have a column in SQL Server that contains data like this:

1.1.1.QuestionText
1.1.1.Question
1.1.1(a).Questions
1.1.2.Questionswithtext
1.1.2(b).Text
10.1.1.Answers
2.1.1.Questions
2.2.2.QuestionText

如何按升序显示?

推荐答案

您碰巧有一个由四部分组成的命名，因此您可以使用 parsename().结果是这样的:

You happen to have a four-part naming, so you can use parsename(). That results in something like this:

order by (case when isnumeric(arsename(col, 4)) = 1 then cast(parsename(col, 4) as int)
          end),
         (case when isnumeric(parsename(col, 3)) = 1 then cast(parsename(col, 3) as int)
          end),
         (case when isnumeric(parsename(col, 2)) = 1 then cast(parsename(col, 2) as int)
          end),
         (case when isnumeric(parsename(col, 1)) = 1 then cast(parsename(col, 1) as int)
          end),
         col

这并不完美，因为您似乎奇怪地放置了括号.但它可能足以满足您的目的.

This isn't perfect, because you seem to have oddly placed parentheses. But it might be good enough for your purposes.

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