Need to perform wildcard search

[Venkata Sirish]

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]

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]

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]

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]

Thanks a lot guys for your replies.

[vinayreddy podduturi]

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]

The answer is already in this topic. * is not a SQL wildcard character.

[vinayreddy podduturi]

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]

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]

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]

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]

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]

Thank you everyone for giving me the reply.