CODEWITHSUNDEEP
mysqlsql-like
Sing O Song
Sing O Song

Reputation: 1

Mysql search with LIKE operator several words

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..

Upvotes: 0

Views: 41

Answers (1)

Akina
Akina

Reputation: 42739

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.

Upvotes: 1

Related Questions