将regex与LIKE一起使用时，首先对字母进行排序，然后对符号SQL进行排序 [英] Using regex with LIKE to sort alphabets first then symbols SQL

问题描述

我有以下MySQL查询

I have the following MySQL Query

SELECT * FROM `travels`.`destinations` AS `Des`
WHERE `Des`.`name` LIKE '%act%' AND `Des`.`sold` = 'N' AND `Des`.`active` = '1'
GROUP BY `Des`.`name` ORDER BY CASE
WHEN `Des`.`name` REGEX 'act*' THEN 0
WHEN `Des`.`name` LIKE '%act' THEN 1
WHEN `Des`.`name` LIKE '%act%' THEN 2
ELSE 3 END, name LIMIT 10

我要实现的目标: actabc actzzz abcact zzzact abcactzzz act-act

What I am trying to achieve: actabc actzzz abcact zzzact abcactzzz act-act

当我按机制使用此分组时，它首先显示连字符结果，应该这样做.我首先要字母，然后是符号，然后是数字.以相同的通配符顺序.

When I use this group by mechanism, it is showing hyphenated result first, which it should. I want alphabets first, then symbols and then numbers. In the same order of wild cards.

这些是单独工作的: 如何对MySQL结果进行排序，字母优先，符号最后? 和 这有点令人困惑(并且也不能按照我的意愿给组分组): mysql regex获取匹配的第一个字母字符的位置

These are working individually: How to sort MySQL results with letters first, symbols last? and This is sort of confusing (and doesn't give the group by as I want either): mysql regex get position of matched first alphabetic character

有什么想法吗?

推荐答案

尝试一下:

SELECT * 
FROM travels.destinations AS D
WHERE D.name LIKE '%act%' AND D.sold = 'N' AND D.active = '1'
ORDER BY CASE WHEN D.name REGEXP '^[a-zA-Z]*$' AND D.name LIKE 'act%' THEN 0
              WHEN D.name REGEXP '^[a-zA-Z]*$' AND D.name LIKE '%act' THEN 1
              WHEN D.name REGEXP '^[a-zA-Z]*$' AND D.name LIKE '%act%' THEN 2
              ELSE 3 
         END, 
         D.name 
LIMIT 10

这篇关于将regex与LIKE一起使用时，首先对字母进行排序，然后对符号SQL进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！