Q

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

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.

This was first published in July 2008

Dig deeper on Oracle development languages

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close