Ask The Oracle Expert: Questions & Answers

Finding a column value inside a user-supplied string

Finding a column value inside a user-supplied string

By  Rudy Limeback, SQL Consultant, r937.com

SearchOracle.com

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':

SELECT id
     , 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.