1

I have SQL Table Design like Listed Below

*-----------------------------------------
| id | title                              |
| -- | -----------------------------------|
| 1  | This is nice pen looking good      |
| 2  | This is nice pen looking elegent   |
| 3  | This is nice pen looking great     |
| 4  | This is nice pen looking best.     |
------------------------------------------

Example Query: Select * from table where title LIKE '%looking%'

when i try to search word "looking" using (like query) or using (Regular Expressions) like example query, I am getting complete string results mentioned below

Results

*------------------------------------
| title                              |
| -----------------------------------|
| This is nice pen looking good      |
| This is nice pen looking elegent   |
| This is nice pen looking great     |
| This is nice pen looking best.     |
-------------------------------------

What i want?

I want predective search words (not complete string)

How can i get below mentioned Results by searching word (looking) using SQL.

*-------------------
| title             |
| ------------------|
| looking good      |
| looking elegent   |
| looking great     |
| looking best.     |
--------------------

Please Suggest how can i write query for geting these types of results? Thanks

M Uzair Qadeer
  • 482
  • 1
  • 8
  • 19
  • Please explain what *you* mean by "predictive search words". – Gordon Linoff Dec 21 '20 at 18:59
  • means when i try to search word "looking", i want result of exact match word and next word of searched word. Searched Word: "looking" results i want "looking good" "looking elegent" "looking great" etc – M Uzair Qadeer Dec 21 '20 at 19:02
  • An interesting question, but wrong toolset. Mysql is a database, not a text analytics tool. It does not have the concept of words. You can obviously combine a set of string functions and achieve a close proxy of what you want within mysql, but the performance will be terrible. – Shadow Dec 21 '20 at 19:06

1 Answers1

0

You can get the first next work using substring_index():

Select t.*,
       substring_index(substring_index(concat(' ', title, ' '), ' looking ', -1), ' ', 1) as next-word
from table
where title LIKE '%looking%';

Here is a db<>fiddle.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • And I can give you several cases when this solution will not work with real-life text. You are oversimplifying it. – Shadow Dec 21 '20 at 22:35