Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

We currently have a solution for this in production, but it's pretty bad. I'd like a fresh perspective on it please.

We have a PERSON table with an ID, LAST_NAME, FIRST_NAME, MIDDLE_NAME, and SUFFIX_NAME. All these columns (except ID of course) are nullable.

What is the best way to index this table, and what is the best way to query for full name with wildcards?

This is used in a web app, and we need to allow the user to search for something like

Smit*, Bob

and it return "Bob Smith", "Bobby Smithson", etc.

We will not allow the user to sort results.

share|improve this question
up vote 2 down vote accepted

You have two options I can think of:

  1. Index for a specific set of user searches


  2. Full Text Search

For option 1, you need to convert your search string "Smit*, Bob" into something you are indexing on, eg: where upper(last_name) like 'SMIT%' and upper(first_name) like 'BOB%' with function based indexes on upper(last_name) and perhaps upper(first_name)

share|improve this answer
+1 Here is the conversion: Replace(UPPER(:FirstName), '*', '%'). – Leigh Riffel Aug 26 '11 at 14:32

We use Lucene.Net to search for contacts on our Oracle database. It's a bit of an investment in coding to set up the indexer, index and searched fields but once it's done you take advantage of the many search options.
Users report the usual problems: enter too wide a search term and get back too many results but it is really good for names where the name entered is, for example: "Robertson" but your search is "Bert"

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.