select records which contains nearby word in string

Question

I am trying to fetch record which contains similar word in string.I know %% trick in mysql to fetch required data like

select * from products where Name like '%LORFAST%'

but what if dont have exact word for filter??? like instead of "LORFAST" if i write "LORFST" then it will not return any record.

select * from products where Name like '%LORFST%'

Expectation : even i miss any character it should return nearby match. above query should return "LORFAST TAB." any idea how to do that??

http://sqlfiddle.com/#!9/30b949/2

Code

create table `products` (
    `Id` int (11),
    `Name` varchar (900)
); 
insert into `products` (`Id`, `Name`) values(1,'LORFAST TAB.');
insert into `products` (`Id`, `Name`) values(1,'SPORIDEX REDIMIX DROP');
insert into `products` (`Id`, `Name`) values(1,'MICROGEST 400MG');
insert into `products` (`Id`, `Name`) values(1,'ANTIPLAR PLUS TAB');
insert into `products` (`Id`, `Name`) values(1,'DECA DURABOLIN  100MG');

I think you are looking for [Levenshtein distance](https://en.wikipedia.org/wiki/Levenshtein_distance). If that is so [here is the implementation](https://stackoverflow.com/questions/560709/levenshtein-distance-in-t-sql). You might need to tweak as per your needs. — Mahesh, Jan 23 '18 at 10:01
Check out SOUNDS LIKE https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_soundex — juergen d, Jan 23 '18 at 10:03

Tim Biegeleisen · Accepted Answer · 2018-01-23T10:29:06.687

You might get some mileage from the SOUNDEX function. Consider the following query:

SELECT 'true'
FROM dual
WHERE SOUNDEX('LORFAST') LIKE SOUNDEX('LORFST');

This would output true because SOUNDEX is smart enough to detect that the two versions of LORFAST sound the same.

In the context of your sample data, let's say you wanted to search the names using LORFST, i.e. an abbreviated form of LORFAST. Then we can use the following query:

SELECT *
FROM products
WHERE SOUNDEX(Name) LIKE CONCAT('%',SOUNDEX('LORFST'),'%');

Follow the link below for a Fiddle of the above query.

As @Coder commented, using something like the Levenshtein distance might be a better general solution.

select records which contains nearby word in string

1 Answers1

Demo