CODEWITHSUNDEEP
mysqlsqlsql-like
Harry P
Harry P

Reputation: 21

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

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.

Upvotes: 2

Views: 1869

Answers (5)

Alieh S
Alieh S

Reputation: 170

This is not a good solution for performance, but if you want to solve the problem only using SQL, you can try this:

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

Field concatenation is used to find a concatenated input value, and different combinations of OR concatenation are used to be independent of the order in which the first and last name are entered. Convert to lowercase - for case insensitive searches. We replace spaces with a % character to search for concatenated field values without worrying about spaces.

Upvotes: 3

Alexandre Dufresne
Alexandre Dufresne

Reputation: 61

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.

Upvotes: 0

prince priyadarshi
prince priyadarshi

Reputation: 53

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.

Upvotes: -1

persian-theme
persian-theme

Reputation: 6638

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%';

Upvotes: 0

Alexandre Dufresne
Alexandre Dufresne

Reputation: 61

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.

Upvotes: 0

Related Questions