按字母、数字、特殊字符顺序排序

cwtwac6a  于 2021-07-26  发布在  Java
关注(0)|答案(2)|浏览(413)
SELECT * FROM details
    ORDER BY cast(SUBSTRING(name,'^[a-zA-Z]+') as varchar) orderDirection NULLS LAST,
    cast(SUBSTRING(name,'^\d+') as numeric) orderDirection NULLS LAST, name orderDirection;

此查询检索asc或desc中的数据取决于我们为“orderdirection”提供的输入,但问题是大写字母和小写字母也将被视为单独的排序。因此,通过在这个查询本身中添加一些东西来帮助我找到合适的查询(因为我正在使用一些通用代码来创建这个查询)
当前结果:

"Came"

"Result"

"Result came"

"came"

"result"

"01 Result"

"# Result"

预期结果:

"Came"

"came"

"Result"

"result"

"Result came"

"01 Result"

"# Result"
ijnw1ujt

ijnw1ujt1#

如果在排序之前将字符串转换为小写,则会得到所需的结果:

SELECT * FROM details
ORDER BY cast(SUBSTRING(LOWER(name,'^[a-z]+')) as varchar) DESC NULLS LAST,
         cast(SUBSTRING(name,'^\d+') as numeric) DESC NULLS LAST,
         LOWER(name) DESC;
vmdwslir

vmdwslir2#

获取使用最新icu库构建的最新postgresql版本,并创建自己的排序规则:

CREATE COLLATION english_weird (
   PROVIDER = 'icu',
   LOCALE = 'en-u-kr-punct-symbol-currency-digit-latn'
);

然后用它来排序:

ORDER BY name COLLATE english_weird DESC

如果您总是希望该列的顺序,请相应地定义它:

ALTER TABLE details
   ALTER name TYPE text COLLATE english_weird;

相关问题