Search all combinations of first, middle and last name

Question

Search all combinations of first, middle and last name

There is a table in my database called author that contains 4 columns:

AuthorID
Pgvbyate
Middlename
Lastname

A user, for example, is looking for Edgar Allan Poe. In our table, Edgar Allan Poe is saved as: firstName - Edgar, middleName - Allan and lastName - Poe. This query is pretty simple. But how to write a query that matches not only Edgar Allan Poe, but also Poe Allan Edgar, Edgar Poe, Allan Poe, Edgar Allan, Allan Edgar Poe without writing all these possible combinations yourself? Also, when a user searches, he / she searches for “Edgar Allan Poe” or “Poe Allan Edgar” in general, and not in specific areas.

+5

sql mysql

Xty83a May 08 '17 at 1:30

source share

3 answers

In any database, you can do something like this:

 where firstName in ('Edgar', 'Allan', 'Poe') and middleName in ('Edgar', 'Allan', 'Poe') and middleName <> firstName and lastname in ('Edgar', 'Allan', 'Poe') and lastname not in (firstname, middleName)

Actually, it’s quite simple to expand to more names if you want - if the names are different, as in your example (if you want to allow authors with duplicate names, just delete lines 3 and 5 from the above query).

However, depending on your database, you can use regular expressions or full-text searches.

+3

Gordon linoff May 08 '17 at 1:34

source share

For a general DBMS, assuming that your goal is to simply simplify your queries, one way would be to add a separate calculated column to the author’s table, which runs when you insert and update.

In other words, enter clumn with the name searchFullName and set its value:

 <space><firstName><space><secondName><space><lastName><space>

Then you query (relatively) simply:

 select something from author where searchFullName like '% Poe %' and searchFullName like '% Edgar %'

It will not be dazzlingly fast for large tables, but it must achieve what you want.

0

paxdiablo May 08 '17 at 1:37

source share

toonice · Accepted Answer · 2017-05-08T15:00:45+0000

Please try the following ...

 DROP PROCEDURE IF EXISTS SimilarNames; DELIMITER // CREATE PROCEDURE SimilarNames( authorFullName VARCHAR( 250 ) ) BEGIN SET @authorFullNameCommad = CONCAT( '\'', REPLACE( authorFullName, ' ', '\', \'' ), '\'' ); SET @selectStatementString := CONCAT( "SELECT authorID,", " firstName,", " middleName,", " lastName ", "FROM author ", "WHERE ( ( firstName IN ( ", @authorFullNameCommad, " ) ) + ( middleName IN ( ", @authorFullNameCommad, " ) ) + ( lastName IN ( ", @authorFullNameCommad, " ) ) ) >=2;" ); PREPARE selectStatement FROM @selectStatementString; EXECUTE selectStatement; DEALLOCATE PREPARE selectStatement; END // DELIMITER ; CALL SimilarNames( 'Edgar Allan Poe' );

This decision starts with creating a PROCEDURE called SimilarNames (after DROP ping any existing versions of PROCEDURE ). This PROCEDURE stores the name passed to it (for example, 'Edgar Allan Poe' ) in the authorFullName parameter authorFullName .

Once started, PROCEDURE starts by converting a string, such as Edgar Allan Poe , to 'Edgar', 'Allan', 'Poe' and storing it in the @authorFullNameCommad variable.

Then the CONCAT() function is used to form the text of the SQL statement, which will give our results. Where authorFullName is Edgar Allan Poe , the following statement is created and stored in @selectStatementString ...

 SELECT authorID, firstName, middleName, lastName FROM author WHERE ( ( firstName IN ( 'Edgar', 'Allan', 'Poe' ) ) + ( middleName IN ( 'Edgar', 'Allan', 'Poe' ) ) + ( lastName IN ( 'Edgar', 'Allan', 'Poe' ) ) ) >=2;

The SQL statement is then PREPARE d and EXECUTE d, creating the desired list when calling PROCEDURE , which can be performed using ...

 CALL SimilarNames( 'Edgar Allan Poe' );

Note that you do not need to declare PROCEDURE after this for the first time. those. the following will work just fine ...

 CALL SimilarNames( 'Edgar Allan Poe' ); CALL SimilarNames( 'James Tiberius Kirk' );

Also note that this particular method is vulnerable to SQL Injection . I can develop a version that is protected from this, if you wish, just recently, and I will go to bed soon.

My instruction has been tested against a sample set created using the following script ...

 CREATE TABLE author ( authorID INT NOT NULL AUTO_INCREMENT, firstName VARCHAR( 50 ), middleName VARCHAR( 50 ), lastName VARCHAR( 50 ), PRIMARY KEY ( authorID ) ); INSERT INTO author ( firstName, middleName, lastName ) VALUES ( 'Edgar', 'Allan', 'Poe' ), ( 'Poe', 'Allan', 'Edgar' ), ( 'Edgar', 'Poe', '' ), ( 'Edgar', '', 'Poe' ), ( '', 'Edgar', 'Poe' ), ( 'Allan', 'Poe', '' ), ( 'Edgar', 'Allan', '' ), ( 'Allan', 'Edgar', 'Poe' ), ( 'Edgar', 'Allan', 'Allan' ), ( 'James', 'Tiberius', 'Kirk' ), ( 'Karl', 'Ignatius', 'von Bach' ), ( 'Edgar', 'Poe', 'xyz' ), ( 'Allanah', 'Poelsen', '' );