SQL 按字符串排序，数字最后出现 [英] SQL Order by string, numbers appear last

问题描述

我有一个查询，它返回一个我想作为排序依据的字符串列.当我按 ASC/DESC 排序时，我得到了相同的结果.这是一个输出示例:

I have a query that return a string column which I would like to sort by. When I sort by ASC/DESC, I'm getting the same result. Here is an output example:

AVI
ksky site
Secure East
Shlomi
VSEStaging
1200165
120186
120186
120186
120032

但我希望在字母字符串之前有数字.例如对于 ASC:

But I would to have the numbers before the alphabetic string. For example for ASC:

1200165
120186
120186
120186
120032
AVI
ksky site
Secure East
Shlomi
VSEStaging

以 DESC 为例:

VSEStaging
Shlomi
Secure East
ksky site
AVI
120032

我目前像这样使用正则表达式:

I currently use REGEX like so:

REGEXP_SUBSTR(UPPER(COLUMN_NAME), '^\D*') ,
TO_NUMBER(REGEXP_SUBSTR(UPPER(COLUMN_NAME), '\d+'))

请帮忙.

推荐答案

您可以使用 regexp_like().对于升序:

You can use regexp_like(). For the ascending sort:

order by (case when regexp_like(col, '^[0-9]+$') then 1 else 2 end) asc,
         col asc

这篇关于SQL 按字符串排序，数字最后出现的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！