• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Need to perform wildcard search

 
Venkata Sirish
Ranch Hand
Posts: 112
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi ranchers,

This is Venkat. Currently iam working on a search functionality in my project. I want to search for the data in the database. I want to search on the keywords(comma seperated data) column. I want to perform a wild card search on that data.

Ex: if the database column contains "java, j2EE, jms, javas".

If my search criteria is " j* " then above record should be retrieved.
Also if my search criteria is " java? " then also above record should be retrieved.


I have gone through lucene API, but it seems to be ocean. I want to implement it ASAP. As iam developing a product it should support for any of these ( MS-SQL and Oracle ) servers.

Iam using struts1.2 Framework.

Please suggest me a simple API that can achieve my requirement.


Thanks in Advance for your time,
Venkat
[ August 25, 2008: Message edited by: Venkata Guru ]
 
Rob Spoor
Sheriff
Pie
Posts: 20750
68
Chrome Eclipse IDE Java Windows
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
In SQL, % and _ are the wildcards characters. % means 0 or more characters, whereas _ means exactly one character.

So if you put your search string between %s you should be fine: %java%

You may need to do some form of conversion between your own search format (only replace ? with % or something similar), but I take it that won't be a problem.
 
Scott Selikoff
author
Saloon Keeper
Posts: 4031
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Just a note, but wildcard searches can really hurt performance in a lot of database systems so be careful how much access users have to such searches. For example, many search systems set a minimum number of characters for a search to 4 to prevent too many records from being searched/returned.
 
Rene Larsen
Ranch Hand
Posts: 1179
Eclipse IDE Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
In an Oracle database it is possible to do a search in more then one way - depending on which index type you are using.

Beside the 'normal' lookup with the '%' - you can create a 'CONTEXT', 'CTXCAT' or 'CTXRULE' index, which have there own search rules and helper functions.

You can read more about it here: Oracle Text
[ August 25, 2008: Message edited by: Rene Larsen ]
 
Venkata Sirish
Ranch Hand
Posts: 112
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks a lot guys for your replies.
 
vinayreddy podduturi
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I too have similar issue with wild card search. I have a textbox. So If you enter a* it should hit the database and retrieve the values which starts with 'a' using like condition. But what is my problem is when I enter a* the string which I am sending is an a* so the like condition doesn't searches for 'a' it is searching 'a*' . This is becoming problem for me. Can any one help me on this.
 
Bear Bibeault
Author and ninkuma
Marshal
Pie
Posts: 65335
97
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The answer is already in this topic. * is not a SQL wildcard character.
 
vinayreddy podduturi
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes thank you for your reply. I know * is not wild card character. But please give me a solution on how to implement wild card search using textbox.
 
Bear Bibeault
Author and ninkuma
Marshal
Pie
Posts: 65335
97
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What information beyond what's already in this topic do you need? Vague requests are impossible to answer.

And the fact that the search term is coming from a text box is irrelevant.
 
Paul Clapham
Sheriff
Posts: 21559
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well, you wouldn't use a textbox (no matter what you mean by that) to implement a wild-card search. You would use SQL to do that. And if you still don't know what the correct wild-card character is, that's in this topic too.
 
Rob Spoor
Sheriff
Pie
Posts: 20750
68
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You can replace non-SQL wildcards into SQL wildcards using the usual String methods. It's really not hard to transform "a*" into "a%". You should think what you would want to do when you input "a%" though - should that also be a wildcard search or should that be a search for a literal "a%"? In the latter case you need to apply a bit more escaping.
 
Jayesh A Lalwani
Rancher
Posts: 2762
32
Eclipse IDE Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hibernate is integerated with Apache Lucene, and you can use Hibernate to do full text searches using HQL without really knowing about how Lucene works. You might want to go through the documentation.

Basically what you need to is add some annotations to you entities. When you persist the entities using Hibernate, it updates the Lucene index in the background. You can control how the data gets indexed using the annotations. TO search you need to use a special Query class that hibernate implements that allows you to search the Lucene index. This Query API is very similar to the normal Query API and returns you a List of entities. This way you don't need to learn the Lucene API, and you get the full power of inverted index implemented in Lucene. Look at the linked documentation. It has some examples.
 
vinayreddy podduturi
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you everyone for giving me the reply.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic