Clyo Clyo - 5 months ago 14
SQL Question

SQL query with two like clauses

I am a complete beginner so I apologise in advance if I write this in a way that is difficult to understand.

I have set up a database and I am using a user interface so the user can obtain certain bits of data. The user needs to be able to search by both first name and surname to return all the information of the person that was searched for. This is the code I have been using below. When I run the program and the enter 'joe bloggs' an exception I made pops up saying 'sorry no match found'. I am certain that joe bloggs is in the database so there is no issue there.

Do I need to take into account the space between the first name and surname that the user enters?

SELECT * FROM TABLE
WHERE UPPER(FirstName) LIKE UPPER(?) AND UPPER(Surname) LIKE (?);

Answer

I am assuming the name entered by the user is just one variable (of some shape or form, in your question referred to by ?) which you want to tie to names in your database.

If this assumption is correct you can do:

SELECT * FROM TABLE
WHERE CONCAT(CONCAT(UPPER(FirstName), ' '), UPPER(Surname)) LIKE (?);

This assumes the user only entered one space in the name. A typo is easy, so it may be better to do the comparison with a replace of any and all spaces in the given name.

As mentioned by others, you can probably replace LIKE with =. Something like a name, you usually want an exact match.