2

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

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.

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

Any ideas?

Community
  • 1
  • 1
Karma
  • 4,688
  • 1
  • 34
  • 62

1 Answers1

0

Try this: 

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
Saharsh Shah
  • 27,975
  • 8
  • 43
  • 82
  • Hi, Thanks... But it shows Syntax error: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'REGEX '^[a-zA-Z]*$' AND – Karma Dec 27 '14 at 07:03
  • 5.5.40-0ubuntu0.14.04.1 - (Ubuntu) – Karma Dec 27 '14 at 07:08
  • Also tried changing '^[a-zA-Z]*$' to '^[a-zA-Z]+' No go – Karma Dec 27 '14 at 07:12