Using 'like' Operator With A Subquery That Returns Multiple Results

Newbie to SQL. Kindly help. I need to count number of records which have a pattern in one of the fields, for multiple patterns. I know how to do it for one pattern, but how do I ge

Solution 1:

You can use like to join the subquery to the table:

SELECT p.pattern, count(a.comment)
FROM (subquery here that returns "pattern"
     ) p leftouterjoin
     TableA a
     on a.comment like'%'||p.pattern||'%'groupby p.pattern;

This assumes that the pattern does not have wildcard characters. If it does, then you do not need to do the concatenation.

This also uses a left outer join so that all patterns will be returned, even with no match.