Problem solve Get help with specific problems with your technologies, process and projects.

Finding a column value inside a user-supplied string

SQL expert Rudy Limeback explains how to find a column value inside a user-supplied string.

I have a table with cat_num with values such as 902S, 444K, and 232N. Now I have to write a query that will retrieve the row that has the matching cat_num in the sentence written by the user. Say the user writes 'This piece of wire has 902S type of characterstics' then the query should return the row that contains 902S cat_num. Thanks.

This can be accomplished with the LIKE operator. The twist to this problem is that the two operands of the LIKE are reversed from their usual usage.

Most examples of LIKE use a table column as the value to search in, with the string supplied by the user as the value to search for. For example, say you have a products table, and the user enters the string 'ultra':

     , description 
  FROM products 
 WHERE description LIKE '%ultra%'

This query finds all products where 'ultra' (the value to search for) is located somewhere within the product description (the value to search in).

In your case, you want to have the string supplied by the user as the value to search in, with the cat_num column as the value to search for. All you need to do is reverse the normal sequence, and append the necessary wildcards, like this:

SELECT cat_num
  FROM yourtable 
 WHERE 'This piece of wire has 902S type ...'
       LIKE '%' || cat_num || '%'

Here, the double pipes are the standard SQL concatenation operator.

This was last published in July 2008

Dig Deeper on Oracle development languages



Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.