How is a search in a Database realized?

Question

Hi
I'm trying to make a search function to my website :
unfortunately i don't know how ?

is a the LIKE enough for a search function ? e.g:

SELECT * FROM Employees WHERE (Title Like 'Title%')

or are there more Professional ways ? ( e.g. regular Expressions )

Answer 1

LIKE is perfect

Answer 2

You may want to look into a full-text search engine such as Lucene.

Lucene is a separate software programming which indexes whatever you tell it to index (e.g., some of the objects in the database). When performing a search, you first use the Lucene API to search the full-text index and retrieve a set of object IDs. Then you perform an easy database retrieval to get the objects with those IDs.

Answer 3

If you're asking how to implement searching capabilities over your web site (like search box at the top of every/some page/s), then it depends on the implementation of your site.

1. If you use a database store to store all site content, then you could use database search for it. But search in content/titles/etc. or use full text search.
2. If you use any CMS/portal solution, it probably already supports search capabilities.
3. If you use static content, you'll have to make it via file searching.

Answer 4

There are a few approaches you could use if you want to implement a system for performing adhoc searches on your relational data:

1. Use an open-source search engine such as Lucene
2. Use the text indexing functionality of your database - if it has it - this depends on which database you are using. Info for: Oracle/SQL Server/MySQL/PostgreSQL
3. Write your own search system using an inverted index - although this is a little pointless if you are able to use option 1 or 2.

Answer 5

With relational databases, you are pretty much left with substring-search (LIKE), which may not be flexible enough, and also only works (efficiently) with short columns (like a title).

So you probably need to use a full text search engine (like Lucene) in addition. In this case there would be a full-text search index outside of the database that you search for keywords.

Some relational databases have optional full-text-search capabilities for their text columns. Using these, you can issue your full-text queries using SQL (and even combine it with queries against other columns). In Oracle it looks something like

SELECT id FROM table WHERE CONTAINS(text_column, 'java AND text', 1) > 0;