CODEWITHSUNDEEP
sqlselectdb2
hertogkj
hertogkj

Reputation: 13

find if result of query is found anywhere in column

I have a column that has varchar values, and need to find if the result of a query can be found anywhere in that column. For example the varchar column may have the following values:

and a select query may have the following results:

I need to return all values in the varchar column that have the value '100' or '200' any where in it. In this case all results in the varchar column should be returned.

This is what I currently have:

select varcharColumn from table where varcharColumn like (select query)

When I try this query I am getting the following error:

"The result of a scalar fullselect, SELECT INTO statement, or VALUES INTO statement is more than one row"

How can I get this query to return a list of the varchar column where any part of it contains a result of the select query?

Upvotes: 1

Views: 390

Answers (2)

Andomar
Andomar

Reputation: 238088

A like can only compare two rows. One of many ways to compare multiple rows is a exists subquery, like:

select  varcharColumn 
from    table t1
where   exists
        (
        select  *
        from    <your other table> t2
        where   t1.varcharColumn like concat('%', t2.varcharColumn, '%')
        )

Upvotes: 3

user7392562
user7392562

Reputation:

You can use something like this

select a.* from testtable1 a, testtable2           
where varcharColumn   like '%'||testtable2.subqueryCOl||'%'

This will match all records with 100, 200. YOu can query your subquery as testtable2 if you need it

Upvotes: 1

Related Questions