CODEWITHSUNDEEP
phpmysqlphpmyadmin
jonboy
jonboy

Reputation: 2749

MySQL Search Where String Contains

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

Upvotes: 0

Views: 10789

Answers (5)

user2182349
user2182349

Reputation: 9782

Extending @David's answer -

SELECT *
FROM `authors`
WHERE `author` LIKE '%Rowling%' AND REGEXP_REPLACE(`author`,'[[:punct:]]','') LIKE '%JK%';

Using REGEXP_REPLACE to remove all punctuation allows you to include the initials or first and middle name as well. The AND allows the order of lastname and firstname/initials to vary.

Upvotes: 0

k0uva
k0uva

Reputation: 181

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.

Upvotes: 0

Bilal Ahmed
Bilal Ahmed

Reputation: 4066

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

Upvotes: 0

Calvin
Calvin

Reputation: 46

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.

Upvotes: 0

David
David

Reputation: 364

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

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

Upvotes: 1

Related Questions