Get the substring from a string if the string contains the substring in mysql

Question

We can do this using programming languages. But getting the result from mysql is hard to find.

I have column in a table that has string values.

ex: saturday monday tuesday wednesday thursday friday sunday

If I search monday from the above string, the query should give only monday as my result.

I need to get only the searched word from the string as a mysql result.

I tried the following query.

 SELECT SUBSTRING_INDEX(usertext,'STORY',0) FROM `usertext`

But it didn't give any result.

I am looking for regular expression to find the solution.

If my question is not clear, kindly comment.

Should it only match complete words? If you were to search for "day" should it return anything then? And are all words unique or can there be two mondays in a string? What happens if you search for monday then? — MrApnea
– MrApnea, Commented May 19, 2017 at 12:15

MrApnea · Accepted Answer · 2017-05-19 12:36:35Z

1

How about this?

select SUBSTR(usertext, INSTR(usertext, 'sunday'), length('sunday'))  
from myTable 
where CONCAT(' ', usertext, ' ') rlike ' sunday ';

If you are sure that you always search for complete words then a simpler version can be:

select SUBSTR(usertext, INSTR(usertext, 'sunday'), length('sunday'))
from myTable
where usertext like '%sunday%';

As far as i know MySql dont have support for regular expression in the select to get out values, only for the where part.

answered May 19, 2017 at 12:36

MrApnea

1,9861 gold badge12 silver badges18 bronze badges

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

1 Comment

Thanks bro. Excellent