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

What should be desired order? What is 'ORDER BY most same chars'? — Alma Do, Nov 22 '13 at 11:20
@AlmaDo most same matching characters. I updated the question. Does that makes sense now? — caramba, Nov 22 '13 at 11:22
@STTLCU something like that would be awesome, but does not change anything...http://sqlfiddle.com/#!2/dc87e/13 — caramba, Nov 22 '13 at 11:30

score 4 · Accepted Answer · edited May 23 '17 at 12:17

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

thank you, I updated the question as I didn't mention this before. the string 'jessy|c' is made by user input so it changes and so the sql query has to change all the time. but thanks for the link, I will try with it — caramba, Nov 22 '13 at 13:43
Then you can create the sql query in your server code (php or perl or whatever), otherwise see the link I posted. — foibs, Nov 22 '13 at 13:46

Siraj Khan · Answer 2 · 2013-11-22T12:27:22.337

1

You can try with this

SELECT CONCAT(`names`,' ',`office`) `bigDataField`
       FROM `item_table` 
HAVING `bigDataField` REGEXP '[a-z] c' order by bigDataField asc;

Hope this will work for you

edited Nov 22 '13 at 12:27

answered Nov 22 '13 at 11:45

Siraj Khan

2,328
17
18

thanks for your answer but I don't see any ordering here. the REGEXP'value' gets changed by users, so it can be a few letters or a few words – caramba Nov 22 '13 at 12:00
you can use order by bigDataField asc; – Siraj Khan Nov 22 '13 at 12:28
but what if there are a few words and letters matching? – caramba Nov 22 '13 at 12:49
please see this link http://82.157.70.109/mirrorbooks/mysqlcrashcourse/0672327120/ch09lev1sec2.html – Siraj Khan Nov 22 '13 at 12:55

Jason Heo · Answer 3 · 2013-11-22T12:24:12.190

0

this works:

SELECT col, IF( LOCATE('Jessy', col) = 0, 0 , LENGTH('Jessy')) as ord
FROM
(
SELECT CONCAT( `names`,' ',`office`) `col`
       FROM `item_table` 
HAVING `col` REGEXP "jessy|c"
) x
ORDER BY ord DESC;

but if there is 3 word in REGEXP, hard to find relevant query.

BTW are you looking for Full Text Search?

edited Nov 22 '13 at 12:24

answered Nov 22 '13 at 12:17

Jason Heo

9,956
2
36
64

thanks, I updated the question as I didn't mention this before. the string 'jessy|c' is made by user input so it changes and so the sql query has to change all the time – caramba Nov 22 '13 at 13:42
@caramba then he or she can search by 'jessy|c|foo'? humm. I wonder if you integrate MySQL and Search Engine. please see http://stackoverflow.com/questions/2271600/elasticsearch-sphinx-lucene-solr-xapian-which-fits-for-which-usage – Jason Heo Nov 22 '13 at 13:45

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

3 Answers3