Mysql search with LIKE operator several words [duplicate]

Question

I have a database structure like below

users

id   |   name
 1   |  Elizabeth Smith
 2   |  Smith Elizabeth
 3   |  Elizabeth  Smith // extra space in between two words
 4   |  Dr.Elizabeth Smith
 5   |  DR. Smith Elizabeth

And I am trying to use Search Form to search names as follows

<form>
 <input type="text" name="name"><br>
 <input type="submit" value="Search Now" name="send" id="send" class="btn btn-danger">
</form>

Now I want to search and Display all names which contains text from input value by visitor when he enters like below values in name input field

Elizabeth
or
Elizabeth Smith
or
Elizabeth  Smith // Extra space between two words
or 
Smith Elizabeth
or
Dr Smith

I am using query as follows

"select * from users where name LIKE '%Smith Elizabeth%';

But output showing only one or two results..

I want to search all rows when user enters : First-Name Last-Name / Last-Name First-Name / Any One Name / Input with two or more spaces...

My database table have more than 60K records.. so unable to edit name columns to remove extra spaces in names etc..

Normalize both values. Convert them to JSON array of strings. Use JSON_OVERLAPS(). Or test FTS. — Akina
– Akina, Commented Jan 21 at 5:01
@Akina Ok... thnx.. Can you provide example code for json or any link for examples... ? — Sing O Song
– Sing O Song, Commented Jan 21 at 5:03
see here how to do it in php stackoverflow.com/questions/19075212/… — Your Common Sense
– Your Common Sense, Commented Jan 21 at 7:07
Searching with like or regexp gives you great flexibility, but none of them can use indexes, unless searching at the beginning at the column only. These solutions won't scale beyond a few 10k of rows. Fulltext searches (2nd duplicate) offer you optimised speed, but less flexibility as you can only search from the start of any word. If you need scalability and flexibility in searching within text, then mysql is not the right backend for you. — Shadow
– Shadow, Commented Jan 21 at 8:11
This pattern may be what you are looking for: WHERE MATCH(name) AGAINST(...) AND {some LIKE or RLIKE}. That will first do a FULLTEXT`, then further filter those results. FTS will ignore the spaces. — Rick James
– Rick James, Commented Jan 22 at 23:50

Akina · Accepted Answer · 2025-01-21 05:20:22Z

A sample. Searching for at least one common word between value and criteria.

-- sample data
CREATE TABLE users (id INT, name VARCHAR(100));
INSERT INTO users VALUES
( 1   ,  'Elizabeth Smith'),
( 2   ,  'Smith Elizabeth'),
( 3   ,  'Elizabeth  Smith'),
( 4   ,  'Dr.Elizabeth Smith'),
( 5   ,  'DR. Smith Elizabeth'),
( 6   ,  'DR. John Silver');

-- add a column with normalized value
-- you may use (stored) generated column also
ALTER TABLE users 
  ADD COLUMN norm_name JSON;
UPDATE users 
  SET norm_name = CONCAT('["', REGEXP_REPLACE(name, '[., ]+', '","'), '"]');
SELECT id, name, CAST(norm_name AS CHAR) norm_name FROM users;

id	name	norm_name
1	Elizabeth Smith	["Elizabeth", "Smith"]
2	Smith Elizabeth	["Smith", "Elizabeth"]
3	Elizabeth Smith	["Elizabeth", "Smith"]
4	Dr.Elizabeth Smith	["Dr", "Elizabeth", "Smith"]
5	DR. Smith Elizabeth	["DR", "Smith", "Elizabeth"]
6	DR. John Silver	["DR", "John", "Silver"]

SET @search_criteria = 'Elizabeth  Smith';
SELECT id, name, CAST(norm_name AS CHAR) norm_name
  , CONCAT('["', REGEXP_REPLACE(@search_criteria, '[., ]+', '","'), '"]') norm_criteria
  , JSON_OVERLAPS(norm_name, CONCAT('["', REGEXP_REPLACE(@search_criteria, '[., ]+', '","'), '"]')) matching
FROM users;

id	name	norm_name	norm_criteria	matching
1	Elizabeth Smith	["Elizabeth", "Smith"]	["Elizabeth","Smith"]	1
2	Smith Elizabeth	["Smith", "Elizabeth"]	["Elizabeth","Smith"]	1
3	Elizabeth Smith	["Elizabeth", "Smith"]	["Elizabeth","Smith"]	1
4	Dr.Elizabeth Smith	["Dr", "Elizabeth", "Smith"]	["Elizabeth","Smith"]	1
5	DR. Smith Elizabeth	["DR", "Smith", "Elizabeth"]	["Elizabeth","Smith"]	1
6	DR. John Silver	["DR", "John", "Silver"]	["Elizabeth","Smith"]	0

SET @search_criteria = 'Dr. Silver';
SELECT id, name, CAST(norm_name AS CHAR) norm_name
  , CONCAT('["', REGEXP_REPLACE(@search_criteria, '[., ]+', '","'), '"]') norm_criteria
  , JSON_OVERLAPS(norm_name, CONCAT('["', REGEXP_REPLACE(@search_criteria, '[., ]+', '","'), '"]')) matching
FROM users;

id	name	norm_name	norm_criteria	matching
1	Elizabeth Smith	["Elizabeth", "Smith"]	["Dr","Silver"]	0
2	Smith Elizabeth	["Smith", "Elizabeth"]	["Dr","Silver"]	0
3	Elizabeth Smith	["Elizabeth", "Smith"]	["Dr","Silver"]	0
4	Dr.Elizabeth Smith	["Dr", "Elizabeth", "Smith"]	["Dr","Silver"]	1
5	DR. Smith Elizabeth	["DR", "Smith", "Elizabeth"]	["Dr","Silver"]	0
6	DR. John Silver	["DR", "John", "Silver"]	["Dr","Silver"]	1

fiddle

Pay attention - the comparing is case-sensitive! If you need in case non-sensitivity then uppercase or lowercase both values and criteria.

Approach with WHERE name LIKE '%Dr%' OR name LIKE '%Smith%' would have been whole world simpler

Collectives™ on Stack Overflow

Mysql search with LIKE operator several words [duplicate]

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related