MySQL Forums
Forum List  »  Full-Text Search

Re: MATCH Keywords with Spaces AGAINST Large Text
Posted by: Peter Brawley
Date: December 06, 2021 12:18PM

Using LIKE instead of MATCH, it's not prohibitively difficult to exclude results that are substrings of other results. Given a keywords table ...

drop table if exists kvals;
create table kvals(kid int unsigned, kval varchar(255) );
insert into kvals values
(1, 'MySQL'),
(2, 'Database Developer'),
(3, 'Software Engineer'),
(4, 'Director of Development'),
(5, 'Developer'),
(6, 'eloper');

... this query finds kvals that are substrings of other kvals ...

select distinct a.kval
from kvals a
join kvals b on a.kval<>b.kval and locate(a.kval,b.kval)>0

... so given the text @s2 ...

set @s2 = "I am a Senior Software Developer with Director of Development experience specializing in JavaScript, JSON, and Web Development. I also have Management experience and have worked in Banking, Cloud, and custom CRM development environments. I am also familiar with .NET and Kittens.";

... this query finds hits in @s2 that aren't substrings of other hits ...

select *
from (
  select kid,kval as hit
  from kvals
  where @s2 like concat('%',kval,'%')
) x
where hit not in(
  select distinct a.kval
  from kvals a
  join kvals b on a.kval<>b.kval and locate(a.kval,b.kval)>0
);
+------+-------------------------+
| kid  | hit                     |
+------+-------------------------+
|    4 | Director of Development |
+------+-------------------------+



Edited 1 time(s). Last edit at 12/06/2021 12:20PM by Peter Brawley.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: MATCH Keywords with Spaces AGAINST Large Text
44
December 06, 2021 12:18PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.