Mysql, How do i query to get name from database on base of string as input?

Question

I have a column name in my table. Which have value Fresh Cream for example.

And input string have value like This product is made of fresh cream and it is fresh made.

I have tried using regular expression by splitting the string on base of string like

    Select * From products where product_name REGEXP ('[[:<:]]This[[:>:]]|[[:<:]]product[[:>:]]|[[:<:]]made[[:>:]]|[[:<:]]of[[:>:]]|[[:<:]]fresh[[:>:]]|[[:<:]]cream[[:>:]]')

But in this case it is also getting the records with the value Chicken with cream. But I want exact match of Fresh cream.

Is there a way to use locate() function like that,

    Select * from products where locate(poducts.product_name , 'This product is made of fresh cream and it is fresh made')>0

Or something like That

"Select * from products where product_name like '%fresh cream%" — Pritamkumar
– Pritamkumar, Commented Sep 11, 2017 at 9:44
But i don't have exact word i have a string in which multiple names could be there — Kaleem
– Kaleem, Commented Sep 11, 2017 at 9:45
CONCAT a % before and after your product_name column value, and then use LIKE to compare with the static search input string. — C3roe
– C3roe, Commented Sep 11, 2017 at 9:46

NewBieBR · Accepted Answer · 2017-09-11 10:02:38Z

2

You need to get all the 'name' then check if your input contain one of those. Then you can use "LIKE % %" expression

list_of_name = SELECT product_name FROM products;
input = "This product is made of fresh cream and it is fresh made.";
foreach (list_of_name as name){
    if (strpos(input, name) !== false){
        matched_name = name;
        break;
    }
expr = "%" . matched_name . "%";
product = SELECT * FROM products WHERE product_name LIKE expr;
}

edited Sep 11, 2017 at 10:02

answered Sep 11, 2017 at 9:54

NewBieBR

3441 gold badge5 silver badges16 bronze badges

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

2 Comments

Kaleem Over a year ago

What if product table have a large number of data? Will it cause speed or any other issue in this case?

NewBieBR Over a year ago

I don't think so, the only data we iterate here is "name" which are strings, I don't think that you have more than 100.000 names in your tables?, it take less then 1s to iterate through a few hundred thousands strings so..

majidarif · Accepted Answer · 2017-09-11 09:45:14Z

0

I think you are over thinking this. Or I'm missing something. But shouldn't this work (and be a lot faster than REGEXP):

WHERE product_name LIKE '%fresh cream%'

That will get all records that contain fresh cream exactly and any preceding or following string.

answered Sep 11, 2017 at 9:45

majidarif

20.4k18 gold badges97 silver badges137 bronze badges

6 Comments

Script47 Over a year ago

Any source for the speed claim?

majidarif Over a year ago

@Script47 REGEXP can never use an index while LIKE can.

Kaleem Over a year ago

Thanks for reply. But i have long string value. How could i get this name from this string. As i don't even know this name exist in the input or not

majidarif Over a year ago

@Kaleem, in that case. LOCATE() is one option or you can move some logic to your scripting language and pick out the right word from the sentence before using it in a query.

majidarif Over a year ago

@Kaleem, there is no fast way of doing what you want to do in MySQL alone. I suggest you get all the product_name first then pick it out from the sentence.

|

Collectives™ on Stack Overflow

Mysql, How do i query to get name from database on base of string as input?

2 Answers 2

2 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related