CODEWITHSUNDEEP
phpmysqlsqlcakephpcakephp-2.0
hellosheikh
hellosheikh

Reputation: 3015

select distinct numbers sql Cakephp

I have a table named messages in which there is a field name mobile numbers. The numbers are in this format:

12345678 and +9112345678 .. so they both are same..the only difference is that in one number the country code is missing.

so I query in the database that 

select the **distinct** numbers from messages tables..

Now it is taking these both numbers as distinct.. but what I want is take these both numbers as one.
How can I do this? and in my DB there are several numbers with different country codes. Some has a country codes and some not, but I want to take both as one. The one in country code and the other without code. How can it be done?

Upvotes: 0

Views: 249

Answers (1)

Joachim Isaksson
Joachim Isaksson

Reputation: 180987

If it's a query that's supposed to be done in "real time", your table contents aren't going to be ideal (read: horrible performance)

That said, you can make an approximation of the correct result if you know all the country codes and their local prefix (for example, a local number in Sweden is something like 092066666 while with country prefix the 0 is removed to +4692066666.

CREATE TABLE country_codes (
  id INT,
  code VARCHAR(5),
  local_prefix VARCHAR(5)
);

INSERT INTO country_codes VALUES
(1, '+46', '0'),
(2, '+91', '');

...and then you can do a query like;

SELECT COUNT(DISTINCT 
  CASE WHEN SUBSTR(number,1,1) = '+' 
       THEN REPLACE(number, 
                    (SELECT code 
                     FROM country_codes
                     WHERE SUBSTR(number, 1, LENGTH(code)) = code), 
                    (SELECT local_prefix 
                     FROM country_codes
                     WHERE SUBSTR(number, 1, LENGTH(code)) = code))
       ELSE number
       END) "distinct numbers"
FROM numbers;

An SQLfiddle to test with.

Upvotes: 2

Related Questions