How to search in a SQL database table

I have a table with the following columns:

1. user_id
2. user_name
3. user_unit
4. user_last_name
5. user_first_name
6. user_email

I want to write a query that the user declares a string that contains a word/part of a word/user_name/user_id/full name/ext. and the query returns all rows the contains the string, sorted by most relevant.

Any offers?

Answer

You can concatenate the columns together and use like:

where (user_id || ' ' || user_name || ' ' || user_unit || ' ' ||
       user_last_name || ' ' user_first_name || ' ' || user_email
      ) like '%' || :input_string || '%'

You haven’t specified the database, so this uses the standard SQL concat operator.