How to select records where at least X fields equal a specific value?

Databases
1

I have a table with fields addr1, addr2, addr3, …, addr10. There are other fields as well as these 10.

Now I want to select all records where between 3 and 6 of the above fields are equal to “some string”. It doesn’t matter which combination. I just need at least 3 and at most 6 of [addr1, …, addr10] to equal “some string”.

Any easy way to do this in the SQL statement?

Thanks

2

Perfect, exactly what I was looking for. Thanks.

3 

select <columns>
  from t
 where 
case when addr1 = 'some string' then 1 else 0 end +
case when addr2 = 'some string' then 1 else 0 end +
case when addr3 = 'some string' then 1 else 0 end +
case when addr4 = 'some string' then 1 else 0 end +
case when addr5 = 'some string' then 1 else 0 end +
case when addr6 = 'some string' then 1 else 0 end +
case when addr7 = 'some string' then 1 else 0 end +
case when addr8 = 'some string' then 1 else 0 end +
case when addr9 = 'some string' then 1 else 0 end +
case when addr10 = 'some string' then 1 else 0 end between 3 and 6
4 

SELECT
     t.id
  FROM
     my_table t
 CROSS
  JOIN
     (
      SELECT 1 num 
      UNION ALL
      SELECT 2
      UNION ALL
      SELECT 3
      UNION ALL
      SELECT 4
      UNION ALL
      SELECT 5
      UNION ALL
      SELECT 6
      UNION ALL
      SELECT 7
      UNION ALL
      SELECT 8
      UNION ALL
      SELECT 9
      UNION ALL
      SELECT 10
     ) i
 WHERE 
     (i.num = 1 AND t.addr1 = 'foo') OR
     (i.num = 2 AND t.addr2 = 'foo') OR
     (i.num = 3 AND t.addr3 = 'foo') OR
     (i.num = 4 AND t.addr4 = 'foo') OR
     (i.num = 5 AND t.addr5 = 'foo') OR
     (i.num = 6 AND t.addr6 = 'foo') OR
     (i.num = 7 AND t.addr7 = 'foo') OR
     (i.num = 8 AND t.addr8 = 'foo') OR
     (i.num = 9 AND t.addr9 = 'foo') OR
     (i.num = 10 AND t.addr10 = 'foo')
 GROUP
    BY
     t.id
HAVING
     COUNT(*) BETWEEN 3 AND 6

That is why you should normalize your data.