CODEWITHSUNDEEP
mysqlsql
paran
paran

Reputation: 199

Select pair numbers in SQL Query

Database:

+------------+
|   Number   |
+------------+
| 0050000235 |
+------------+
| 5532003644 |
+------------+
| 1122330505 |
+------------+
| 1103220311 |
+------------+
| 1103000011 |
+------------+
| 1103020012 |
+------------+

To select numbers having pair of "0" 3 times I tried:

SELECT * FROM numbers
WHERE Number LIKE "%00%00%00%"
    OR Number LIKE "%00%0000%"
    OR Number LIKE "%0000%00%"
    OR Number LIKE "0000%00%"
    OR Number LIKE "%00%0000"   
    OR Number LIKE "00%0000%"
    OR Number LIKE "%0000%00"
    OR Number LIKE "%0000%00"
    OR Number LIKE "%000000%" 
    OR Number LIKE "000000%"
    OR Number LIKE "%000000"

This results me:

0050000235

But the way I am using, I think it's not a clean method.

Question How to fetch numbers having 3 pairs in it with clean SQL query?

The result will be:

0050000235, 5532003644, 1122330505, 1103220311 & 1103000011

Upvotes: 0

Views: 867

Answers (3)

Paul Spiegel
Paul Spiegel

Reputation: 31802

where Number rlike '((00|11|22|33|44|55|66|77|88|99).*){3}'

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269933

How about using regular expressions?

where number regexp '00.*00.*00'

Or slightly shorter:

where number regexp '(00.*){3}'

You can readily generalize this to any two numbers:

where number regexp '([0-9]{2}.*){3}'

If you want to ensure exactly six '0' (as opposed to more):

where number regexp '^[^0]*00[^0]*00[^0]*00[^0]*$'

Upvotes: 0

forpas
forpas

Reputation: 164099

Create a series of numbers from 0 to 9 with UNION ALL and cross join to the table.
Each of these numbers will be doubled and replaced in the column of the table with an empty string. The difference in length of each replacement will be summed and if it is greater than 6 this means that there exist at least 3 pairs:

select 
  n.number
from (
  select 0 d union all select 1 d 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
) s cross join numbers n
group by n.number                     
having sum(
  length(n.number) - length(replace(n.number, repeat(d, 2), '')) 
) >= 6

See the demo.
Results:

| number     |
| ---------- |
| 0050000235 |
| 1103000011 |
| 1103220311 |
| 1122330505 |
| 5532003644 |

Upvotes: 1

Related Questions