Is there a way to ORDER by REGEXP in MySQL statement?

Question

what I have

SELECT CONCAT(`names`,' ',`office`) `bigDataField`
   FROM `item_table` 
HAVING `bigDataField` REGEXP "jessy|c";

returnes also Data which just contains letter "c" so I would like to ORDER BY most same matching characters, is that possible ?

NOTE: words and characters get changed by user input. So it can be only one character or a few or even a few words.

sql fiddle http://sqlfiddle.com/#!2/dc87e/1

Thanks for all the help

Answer 1

You can order by any expression.

regexp returns the number of matches for the specified regex

So, this:

order by `bigDataField` regexp 'c' desc

will order your data by the bigDataField that has the most c's in it as first so I guess it's not what you are looking for. You can use multiple CASE-WHENs to check the length of the pattern matching (warning: bad performance - not recommended for big tables)

try this

SELECT CONCAT(`names`,' ',`office`) `bigDataField`, 
  CASE WHEN CONCAT(`names`,' ',`office`) regexp 'jessy' > 0 then length('jessy') * (CONCAT(`names`,' ',`office`) regexp 'jessy') ELSE 
      CASE WHEN CONCAT(`names`,' ',`office`) regexp 'c' > 0 then length('c') * (CONCAT(`names`,' ',`office`) regexp 'c') ELSE 0 END
  END as charmatchcount

FROM `item_table` 
HAVING `bigDataField` REGEXP "jessy|c"
ORDER BY charmatchcount desc

To avoid the above ugliness you must use an external library, or create your own function. You may find this thread helpful MySQL - Return matching pattern in REGEXP query