Mysql - Like & Regexp usage together

Question

I am optimizing my query since full text search returns irrevelant result when there is numbers and repetitive keywords in text.

What I want to do is to extract numbers on text and add X amount of point to relevance when sorting the result.

Everything works smoothly besides one thing;

When I want to extract and prioritize result with number Z, it also counts other numbers that includes number Z in any part of it.

For Example;

Sample Data
###############
Text 55.A
Text 55_B
Text #55ABC
Text 551234.
Text 55677#
Text 556

    Query
###############
... CASE WHEN (myTable.title like "% 55%") THEN ...

Expected output
###############
Text 55.A
Text 55_B

Actual output
###############
Text 55.A
Text 55_B
Text #55ABC
Text 551234.
Text 55677#
Text 556

How could I use REGEXP with LIKE, there can be symbols and characters after number I have given.

Thanks in advance

Try REGEXP '([[:<:]]|_)55([[:>:]]|_)', or - if it is MySQL 8.x+ - REGEXP '(\\b|_)55(\\b|_)'. — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Jan 10, 2019 at 11:43
@WiktorStribiżew Thank you very much, this even deals with spaces which was problem for some cases to me. It works just as intended. I was thinking to use Like and Regex together thanks to my "junior" skills.. :) — Pelin
– Pelin, Commented Jan 10, 2019 at 11:49

Wiktor Stribiżew · Accepted Answer · 2019-01-10 11:50:57Z

1

You may use

REGEXP '([[:<:]]|_)55([[:>:]]|_)'

If you are using MySQL 8.x and newer that use ICU regex library use

REGEXP '(\\b|_)55(\\b|_)'

See the regex demo

The (\\b|_) matches a word boundary or a _, the ([[:<:]]|_) matches a starting word boundary or _ and ([[:>:]]|_) matches a trailing word boundary or _.

answered Jan 10, 2019 at 11:50

Wiktor Stribiżew

631k41 gold badges502 silver badges632 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mysql - Like & Regexp usage together

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related