How could I make a fullname search work with a seperate last and firstname?-CodePudding

I'm new to SQL and I'm trying to make a basic query work.

This is the table and it's layout that I'm trying to search against: https://prnt.sc/20wj54g

What I'm aiming to do is have a search term input, then have the input be used to search for names in the database by fullname.

This is the current query I have now:

SELECT *
FROM personnel p
LEFT JOIN department d ON (d.id = p.departmentID)
LEFT JOIN location l ON (l.id = d.locationID)
WHERE p.firstName LIKE '%Robert Heffron%' OR p.lastName LIKE '%Robert Heffron%';

This doesn't work as there is no last or firstname which contains the whole string "Robert Heffron" however this means if the user typed in that string looking for that person by the full name they wouldn't find them.

I'm currently using PHP and JS to display the data but I'm struggling with the SQL part. If anyone could help I'd be very grateful.

CodePudding user response：

I would suggest adding a full name field to your database table and whenever you create a "personnel" in your code, you concatenate the first and last name:

Fullname = Firstname   " "   Lastname

if you don't like this, maybe use a string split function (in php it might be .explode()) and then adding WHERE OR statements for each substring for first and last name.

CodePudding user response：

Use the following combination to find people whose name is Robert and whose last name is Heffron

Just find Robert Heffron

WHERE p.firstName LIKE '%Robert%' AND p.lastName LIKE '%Heffron%';

find all first names that have Robert and all last names that have Heffron

WHERE p.firstName LIKE '%Robert%' OR p.lastName LIKE '%Heffron%';

CodePudding user response：

A solution that wouldn't ask you to change the table and having only one input variable could be something like this:

DECLARE @FullName AS VARCHAR(100)
SET @FullName = 'Robert Heffron'
SET @FullName = REPLACE(@FullName, ' ', '.')
SELECT....
WHERE p.firstname LIKE '%'  ParseName(@FullName, 2)   '%' OR 
    p.firstname LIKE '%'  ParseName(@FullName, 1)   '%' OR 
    p.lastnameLIKE '%'  ParseName(@FullName, 2)   '%' OR 
    p.lastname LIKE '%'  ParseName(@FullName, 1)   '%'

Downside in my opinion is if the user enters weird amount of spaces in the name. I have tested replacing the array number 1 or 2 in the ParseName with a number which was impossible, like 7, and it basically just ignores it.. no errors thrown or whatever. If you use this, you could test it on your side just to make sure tho.

CodePudding user response：

You can use concat for the columns and can use "HAVING" clause on the concatenated column. I hope it will work for you.

SELECT * FROM personnel p LEFT JOIN department d ON (d.id = p.departmentID) LEFT JOIN location l ON (l.id = d.locationID) HAVING CONCAT(firstName,' ',lastName) LIKE '%Robert Heffron%';

I hope this snippet will work for you.