Manipulate the output of select query

Question

I have requirements to retrieve the unique mobile number from database. The issue is the number is not having a fixed format some starts with +CountryCode or 00CountryCode or without + and country code

So I want to manipulate them to have a single format using If. But i don't want to change there value in the database I just want to change it in the query itself.

I don't know how to do this with DISTINCT

this is what i did so far

SELECT DISTINCT PhoneNumber
FROM User
where  (len(PhoneNumber) = 13 or len(PhoneNumber) = 10 or phoneNumber like '311%' or phoneNumber like '00311%' )

Answer 1

Use case when. Here is the solution for MySQL

select distinct case when length(phone) = 10 then concat('00382', right(phone ,(length(phone)-1)))
                     when length(phone) = 13 then replace(phone, '+', '00')
                     when length(phone) = 12 then concat ('00', phone)
                     else phone
                end 
from myTable;     

Here I assumed you will have phone numbers that are 10 digits long for exaple: 0123456789 I also assumed when the numbers have country code then the first digit is not displayed, for example : 00382123456789 (14 digits long).

So having that assumed I have done this:

• If number is long 10 digits (0123456789) add country code (00382) and remove first digit (0)

• If number is long 13 digits then treplace + with 00

• If number is long 12digits then add 00

Here is the DEMO

And here is the solution for SQLServer:

select distinct case when len(phone) = 10 then concat('00382', right(phone ,(len(phone)-1)))
                     when len(phone) = 13 then replace(phone, '+', '00')
                     when len(phone) = 12 then concat ('00', phone)
                     else phone
                end 
from myTable;    

Answer 2

SELECT DISTINCT RIGHT(PHONENUMER,10) FROM USER

Is this useful, i.e. get last 10 digits ?