MySQL Search Where String Contains

Question

I would like to search a MySQL database for author names. The issues is the same author name can be spelled (or entered into the database) in a variety of ways. I need to check for similar names.

A sample of my authors table is as follows;

+---------+-------------+
| id      | author      |
+---------+-------------+
| 1       | JK Rowling  |
+---------+-------------+
| 2       | J.K Rowling |
+---------+-------------+
| 3       | Rowling JK  |
+---------+-------------+
| 4       | Rowling J.K |
+---------+-------------+
| 5       | JK. Rowling |
+---------+-------------+
| 6       | Jim Jones   |
+---------+-------------+

I want to find all books by 'J.K Rowling', so the query I am using is;

SELECT *
FROM `authors`
WHERE `author` LIKE '%J.K Rowling%'

Returns no results.

SELECT *
FROM `authors`
WHERE `author` LIKE 'J.K Rowling%'

Returns no results.

SELECT *
FROM `authors`
WHERE `author` LIKE '%J.K Rowling'

Returns no results.

How should I structure the query in order to return similar authors.

Thanks

Shouldn't this work: SELECT * FROM `authors` WHERE `item_author` LIKE '%rowling%'; ? — informer, May 05 '17 at 10:18
you need similarity. see http://stackoverflow.com/questions/3338889/how-to-find-similar-results-and-sort-by-similarity — katona.abel, May 05 '17 at 10:18
What is the datatype for the author column? SELECT * FROM `authors` WHERE `author` LIKE '%J.K Rowling%' should definitely return the row with id 2 — Praveen Govind, May 05 '17 at 10:22
The datatype is `varchar`. ` SELECT * FROM authors WHERE author LIKE '%J.K Rowling%' ` returns 0 results. Thanks anyway — jonboy, May 18 '17 at 14:40

score 0 · Answer 1 · answered May 05 '17 at 10:18

0

It seems that the similar name is Rowling, so I should try with this.

SELECT *
FROM `authors`
WHERE `author` LIKE '%Rowling%'

answered May 05 '17 at 10:18

David

354
6
19

I'm unable to do that, I am searching a whole string. The whole string being whatever is in the row :/ Thanks though – jonboy May 18 '17 at 14:38

score 0 · Answer 2 · answered May 05 '17 at 10:25

0

That's because you use %J.K Rowling% which means the process will only search on rows that has J.K Rowling and no more no less. Based on your datatabe, there's no J.K Rowlingon it. You might wanna try Rowling or something like that.

answered May 05 '17 at 10:25

Calvin

46
7

J.K Rowling is in the data set – Praveen Govind May 05 '17 at 10:26

score 0 · Answer 3 · answered May 05 '17 at 10:25

your problem is "."

According to the MySQL docs, the only special characters when using the LIKE operator are "%" (percent: matches 0, 1, or many characters) and "_" (underscore: matches one and only one character). http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html

A "." (period) does have special meaning for MySQL's REGEXP operator, but it should still match a literal period in your column. http://dev.mysql.com/doc/refman/5.0/en/regexp.html

score 0 · Answer 4 · answered Sep 15 '21 at 07:26

I ran your MySQL code on https://paiza.io/

create table authors(id integer, author varchar(100));
insert into authors(id, author) values(1, "J.K Rowling");
insert into authors(id, author) values(2, "JK Rowling");
insert into authors(id, author) values(3, "Rowling JK");
insert into authors(id, author) values(4, "Rowling J.K");
insert into authors(id, author) values(5, "JK. Rowling");
insert into authors(id, author) values(6, "Jim Beam");

SELECT *
FROM `authors`
WHERE `author` LIKE '%J.K Rowling%'

and the result I got is:

id  author
1   J.K Rowling

So the code checks out, should be working, maybe your database settings are different somehow.

MySQL Search Where String Contains

4 Answers4