Mysql return only pattern match from column

Question

I have a column with a product description. I want to do a search on this column with a regexp and only return the match from the regexp instead of the complete content of the column.

i.e. I have a description

This magnificent bead is high on detail

and

The bracelet has a magnetic closure

SELECT description FROM products WHERE description RLIKE"'magn.*\s"

This should return magnificent and magnetic

SELECT description FROM products WHERE description RLIKE"'magni.*\s"

This should return magnificent

But of course this current queries return the complete descriptions. Any pointers how I could do this. MYSQL 5.5

Oh okay.. Excuse me.. Never seen that before :) @Jeroen Swets — Syno
– Syno, Commented Jul 20, 2018 at 6:50

Paul Campbell · Accepted Answer · 2018-07-20 22:32:29Z

2

If you are using MySQL version 8.0, you can use regexp_substr() for this purpose. This function wasn't available in earlier versions.

https://dev.mysql.com/doc/refman/8.0/en/regexp.html#function_regexp-substr

UPDATE

Here's another crack at it which should be 5.5 friendly. Assumes that the supplied regex is at the start of the required match.

SELECT 
SUBSTRING_INDEX(
  SUBSTRING(`description`, LOCATE('magni', `description`)), ' ', 1) 
FROM table1 
WHERE `description` REGEXP 'magni';

And a working version 8.0 example for whoever upvoted my previous effort

SELECT REGEXP_SUBSTR('Those magnificent men in their flying machines', '[[:space:]]magni[^ ].*[[:space:]]');

edited Jul 20, 2018 at 22:32

answered Jul 20, 2018 at 6:51

Paul Campbell

1,9862 gold badges14 silver badges19 bronze badges

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

3 Comments

Jeroen Swets Over a year ago

Unfortunately the hoster is running 5.5

Paul Campbell Over a year ago

3 versions behind = time for new hosting.

Jeroen Swets Over a year ago

Probably, but that doesn't answer my question.

Komal · Accepted Answer · 2018-07-20 08:22:51Z

0

    You can use REGEXP in mySql to solve your problem like:

SELECT description FROM products WHERE description REGEXP 'magn.*'
    The above query will give u result magnificent and magnetic

SELECT description FROM products WHERE description REGEXP 'magni.*'
    The above query will give u result magnificent

answered Jul 20, 2018 at 8:22

Komal

1

1 Comment

Jeroen Swets Over a year ago

This is the simplified version of my query. The matching of the pattern is not the problem, Its the returning value of the select which is the problem.

Collectives™ on Stack Overflow

Mysql return only pattern match from column

2 Answers 2

UPDATE

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

UPDATE

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related