find if result of query is found anywhere in column

Question

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:

abc100
00100
100
200

and a select query may have the following results:

100
200

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?

What database are you using?! You've tagged three different database engines... — mituw16, Apr 27 '18 at 17:10
You can not use a like with another select query (unless only 1 value is being returned) — Brad, Apr 27 '18 at 17:12
Typically you can do this with an IndexOf function, does db2 have a similar function? — Kyle B, Apr 27 '18 at 17:33

Andomar · Answer 1 · 2018-04-27T17:25:29.737

3

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, '%')
        )

edited Apr 27 '18 at 17:25

answered Apr 27 '18 at 17:14

Andomar

232,371
49
380
404

score 1 · Answer 2 · answered Apr 27 '18 at 17:20

1

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

answered Apr 27 '18 at 17:20

This would return the row multiple times if it matches more than once. (Why use ANSI-89 joins btw? https://stackoverflow.com/a/334245/50552) – Andomar Apr 27 '18 at 17:23

find if result of query is found anywhere in column

2 Answers2