CODEWITHSUNDEEP
postgresql
warvariuc
warvariuc

Reputation: 59604

Using LIKE with a list

I need to select records where a column value is LIKE any of the values an a list:

select * 
from bulkjob
where bulkjob_owner LIKE (
    SELECT '%' || sys_user_login
    FROM sys_user
    WHERE sys_user_type_id IN (3, 4, 5)
)

This doesn't work:

ERROR: more than one row returned by a subquery used as an expression

Is there a way to accomplish this, maybe using Postgresql functions?

Upvotes: 1

Views: 96

Answers (1)

Glenn
Glenn

Reputation: 9150

This does a cross product, so you could get duplicates if the suffixes (ie sys_user_login names overlap: say 'joe' and 'marry-joe'). So you may need to do a distinct, or subquery.

SELECT b.*
  FROM bulkjob b
      ,sys_user s
  WHERE b.bulkjob_owner LIKE '%' || s.sys_user_login
    AND s.sys_user_type_id IN (3,4,5)

Upvotes: 2

Related Questions