File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Search on name using LIKE statement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCM Java EE 6 Enterprise Architect Exam Guide this week in the OCMJEA forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Search on name using LIKE statement" Watch "Search on name using LIKE statement" New topic
Author

Search on name using LIKE statement

Jeppe Sommer
Ranch Hand

Joined: Jan 07, 2004
Posts: 270
Hello,

I have a search textfield on my website and want to be able to search on peoples firstname and lastname stored in the database. I have a database record for firstname and one for lastname.

If I type in the name 'Peter Anderson' and use a SQL statement like the below:

SELECT * FROM customer WHERE firstname LIKE '%firstname%' OR lastname LIKE '%lastname%'

- then I won�t get any results as Peter doesn�t exist as a lastname and Anderson doesn�t exist as firstname...

So what can I do?
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


I have a database record for firstname and one for lastname.

Do you mean you have a record with a field for first name and a field for last name? Or do you use two records?


If I type in the name 'Peter Anderson' and use a SQL statement like the below:

SELECT * FROM customer WHERE firstname LIKE '%firstname%' OR lastname LIKE '%lastname%'

- then I won�t get any results as Peter doesn�t exist as a lastname and Anderson doesn�t exist as firstname...

I'm not sure what you are asking here. If 'Peter' doesn't exist as a lastname then you wouldn't expect any results if you search for it? Or am I missing something?
[ August 08, 2007: Message edited by: Paul Sturrock ]

JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Jeppe Sommer
Ranch Hand

Joined: Jan 07, 2004
Posts: 270
Originally posted by Paul Sturrock:

Do you mean you have a record with a field for first name and a field for last name? Or do you use two records?


Yes I was unclear. I have a record with a field for first name and a field for last name.



If I type in the name 'Peter Anderson' and use a SQL statement like the below:

SELECT * FROM customer WHERE firstname LIKE '%firstname%' OR lastname LIKE '%lastname%'

- then I won�t get any results as Peter doesn�t exist as a lastname and Anderson doesn�t exist as firstname...
[/code]
I'm not sure what you are asking here. If 'Peter' doesn't exist as a lastname then you wouldn't expect any results if you search for it? Or am I missing something?[/QB]

Lets say Peter Anderson is stored in the database. And I just have one textfield in the webform. If I "think" there is a person called Peter Anderson, then how can I search on his name and get information on him if I type in the whole name (Peter Anderson)?
[ August 08, 2007: Message edited by: Jeppe Fjord ]
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

If you have two fields in the database does it not make more sense to have two input fields in your GUI? That way you know the string "Peter" represents a first name, so you can easily do this:

If you've got one input you will need to first try to work out which part of its input corresponds to which field. So its easy if you have "Peter Anderson" ; you just split the string. But what if it just contains "Anderson"? Is that a first name or a last name? Or what if it is "Jon Dahl Tomasson"? If the firstname double-barrelled or the lastname? Or what if you have an Chinese name, where the last name comes first?

If you have defined attributes, it makes like much easier to tailor your GUI to match them. If you are trying to do "free text" searching (sort of google-like behaviour) you probably don't want to use the database directly, rather an IR tool like Lucene.
Jeppe Sommer
Ranch Hand

Joined: Jan 07, 2004
Posts: 270
Okay, thanks for your reply. I got it :-)

The topics you just mentioned was just what I hoped could be solved somehow, but I see they just can�t without making several input fields.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Search on name using LIKE statement