MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


8.0.30


CREATE TABLE books (id INTEGER, BookDetails VARCHAR(500));

INSERT INTO books VALUES
(1  ,'A Tale of Two Cities|Charles Dickens|Penguin'),
(2  ,'And Then There Were None,Agatha Christie Random House'),
(3  ,'Adventures of Sherlock Holmes Arthur Conan Doyle-Harper Collins'),
(4  ,'Integrated Physics, Sarah Ann Barnes & Nobles'),
(5  ,'Integrated Electronics Millman & Halkias McGraw'),
(6  ,'Integrated Electronics|Millman|McGraw');

Records: 6  Duplicates: 0  Warnings: 0


SELECT * FROM books;


id
BookDetails


1
A Tale of Two Cities|Charles Dickens|Penguin

2
And Then There Were None,Agatha Christie Random House

3
Adventures of Sherlock Holmes Arthur Conan Doyle-Harper Collins

4
Integrated Physics, Sarah Ann Barnes & Nobles

5
Integrated Electronics Millman & Halkias McGraw

6
Integrated Electronics|Millman|McGraw


/*
If I go with breaking it into single word AND 
(like BookDetails like '%Integrated%' and BookDetails like '%Electronic%' and BookDetails like '%Halkias%') 
it will fail to match 6, but will match 4 (wrong!).
*/
SELECT *
FROM books
WHERE BookDetails like '%Integrated%' 
  and BookDetails like '%Electronic%' 
  and BookDetails like '%Halkias%'


id
BookDetails


5
Integrated Electronics Millman & Halkias McGraw


SELECT 
  id,
  BookDetails,
  CASE WHEN BookDetails like '%Integrated%'  THEN 1 ELSE 0 END +
  CASE WHEN BookDetails like '%Electronic%'  THEN 1 ELSE 0 END +
  CASE WHEN BookDetails like '%Halkias%'  THEN 1 ELSE 0 END as Score
FROM books
WHERE BookDetails like '%Integrated%' 
   or BookDetails like '%Electronic%' 
   or BookDetails like '%Halkias%'
ORDER BY 3 DESC;


id
BookDetails
Score


5
Integrated Electronics Millman & Halkias McGraw
3

6
Integrated Electronics|Millman|McGraw
2

4
Integrated Physics, Sarah Ann Barnes & Nobles
1


ALTER TABLE books ADD FULLTEXT(BookDetails);

Records: 0  Duplicates: 0  Warnings: 1


SELECT 
  id,
  BookDetails,
  MATCH (BookDetails)
     AGAINST ('Integrated Electronic Halkias' IN NATURAL LANGUAGE MODE) as Score
FROM books
WHERE  MATCH (BookDetails)
     AGAINST ('Integrated Electronic Halkias' IN NATURAL LANGUAGE MODE)
ORDER BY 3 DESC;


id
BookDetails
Score


5
Integrated Electronics Millman & Halkias McGraw
0.6961383819580078

4
Integrated Physics, Sarah Ann Barnes & Nobles
0.0906190574169159

6
Integrated Electronics|Millman|McGraw
0.0906190574169159


-- Note the difference between `Electronic` and `Electronics`
SELECT 
  id,
  BookDetails,
  MATCH (BookDetails)
     AGAINST ('Integrated Electronics Halkias' IN NATURAL LANGUAGE MODE) as Score
FROM books
WHERE  MATCH (BookDetails)
     AGAINST ('Integrated Electronics Halkias' IN NATURAL LANGUAGE MODE)
ORDER BY 3 DESC;


id
BookDetails
Score


5
Integrated Electronics Millman & Halkias McGraw
0.9237831234931946

6
Integrated Electronics|Millman|McGraw
0.3182637691497803

4
Integrated Physics, Sarah Ann Barnes & Nobles
0.0906190574169159


-- typo's
SELECT 
  id,
  BookDetails,
  MATCH (BookDetails)
     AGAINST (' Electronic Halkios' IN NATURAL LANGUAGE MODE) as Score
FROM books
WHERE  MATCH (BookDetails)
     AGAINST (' Electronic Halkios' IN NATURAL LANGUAGE MODE)
ORDER BY 3 DESC;


id
BookDetails
Score

id	BookDetails
1	A Tale of Two Cities\|Charles Dickens\|Penguin
2	And Then There Were None,Agatha Christie Random House
3	Adventures of Sherlock Holmes Arthur Conan Doyle-Harper Collins
4	Integrated Physics, Sarah Ann Barnes & Nobles
5	Integrated Electronics Millman & Halkias McGraw
6	Integrated Electronics\|Millman\|McGraw

id	BookDetails	Score
5	Integrated Electronics Millman & Halkias McGraw	0.6961383819580078
4	Integrated Physics, Sarah Ann Barnes & Nobles	0.0906190574169159
6	Integrated Electronics\|Millman\|McGraw	0.0906190574169159

id	BookDetails	Score
5	Integrated Electronics Millman & Halkias McGraw	0.9237831234931946
6	Integrated Electronics\|Millman\|McGraw	0.3182637691497803
4	Integrated Physics, Sarah Ann Barnes & Nobles	0.0906190574169159