Query that checks for a value A to appear at least B number of times in C columns

Question

I'm trying to select rows from a table where some value a is within c columns at least b times.

Here is a simplified example of the data I'm working with. I'm trying to find rows that have an h in at least 2 of the greetings.

greeting1	greeting2	greeting3	farewell1	farewell2
hi	hello	hey there	goodbye	peace
hi	sup	yo	Au revoir	see ya
yo	hola	ayyyy	hang tight	reverse-hi
hola	sup	hello	see ya	peace
hello	yo	hola	hang tight	ciao
ayyy	yo	hola	hang tight	ciao

And below are the rows I'd like to grab:

greeting1	greeting2	greeting3	farewell1	farewell2
hi	hello	hey there	goodbye	peace
hola	sup	hello	see ya	peace
hello	yo	hola	hang tight	ciao

(I italicized and bolded for easier ability to see)

I'm just learning SQL, so this might be basic. I was having some difficulties. I know getting the rows and checking all greeting1 would look something like:

SELECT *
FROM
  GreetingsAndFarewell
WHERE
  greetings1 LIKE '%h%'
  AND greetings2 LIKE '%h%'
  AND greetings3 LIKE '%h%'

But what if I only care if at least 2 greetings have an h in them?

Here are some of the similar questions I've seen, but I don't think either of them really address this:

5760335 31577623

If you add your sample data as DDL+DML you make it *much* easier for people to assist. — Dale K, Oct 29 '21 at 23:16
And SQL Server <> Google Big Query - please only tag the relevant product. — Dale K, Oct 29 '21 at 23:17

score 2 · Accepted Answer · answered Oct 29 '21 at 23:20

You could use try summing CASE expressions which check for the letter h in each of the 3 greetings:

SELECT *
FROM GreetingsAndFarewell
WHERE CASE WHEN greetings1 LIKE '%h%' THEN 1 ELSE 0 END +
      CASE WHEN greetings2 LIKE '%h%' THEN 1 ELSE 0 END +
      CASE WHEN greetings3 LIKE '%h%' THEN 1 ELSE 0 END >= 2;

On BigQuery you might be able to directly sum boolean expressions and simplify to:

SELECT *
FROM GreetingsAndFarewell
WHERE greetings1 LIKE '%h%' + greetings2 LIKE '%h%' + greetings3 LIKE '%h%' >= 2;

Thanks, I'm not having luck on the second portion of your answer, but the first works perfectly. — radiomime, Nov 01 '21 at 16:09

score 1 · Answer 2 · answered Oct 29 '21 at 23:31

1

consider below approach (BigQuery)

select *
from GreetingsAndFarewell
where (
  select count(1)
  from unnest([greeting1, greeting2, greeting3]) greeting
  where greeting like '%h%'
) >= 2

if applied to sample data in your question - output is

answered Oct 29 '21 at 23:31

Mikhail Berlyant

165,386
8
154
230

1

Formatted text is the preferred method of displaying data. It works better for everyone. – Dale K Oct 30 '21 at 00:53

Query that checks for a value A to appear at least B number of times in C columns

2 Answers2