Update Query - T SQL

Question

SQL Server: 2008 R2 Version

I need a t-sql query to remove country code from all the phone numbers that has prefix code like if the phone number is 8522345678901 for hong kong, I want to remove 852 and the result should be 2345678901. The below tables are below and connecting column is country code i.e HK, USA, UK, AUS so based on the country code, I need to trim the prefix DialingPhoneCode from PhoneNumbers table columns i.e mobile, fax, landline.

Sample Data for PhoneNumbers

Mobile     , Fax,        Landline,   CountryCode

61298765432, 228765432  , 598765432,  AUS
61298765432, 61228765432, 598765432,  AUS
85228157711, 28157711   , 85228157711,HK

Sample Data for PhoneCodes

DialingPhoneCode, CountryCode

61 ,             AUS
851,             HK

Expected Output for PhoneNumbers

Mobile,Fax, Landline, CountryCode

298765432, 228765432, 598765432, AUS
298765432, 228765432, 598765432, AUS
28157711 , 28157711 , 28157711 , HK

Microsoft T-SQL

CREATE TABLE [dbo].[PhoneNumbers](
    [Mobile] [varchar](500) NULL,
    [Fax] [varchar](500) NULL,
    [Landline] [varchar](500) NULL,
    [CountryCode] [varchar](500)  NULL
) ON [PRIMARY]

GO


CREATE TABLE [dbo].[PhoneCodes](
    [DialingPhoneCode] [varchar](500) NULL,
    [CountryCode] [varchar](500)  NULL
) ON [PRIMARY]

GO

You've told us what you want to do, but you haven't asked us a question. How do you know if a phone number has a country code at the start? Does every number in your table have a country code? If it does, is it always consist of 3 digits? I assume not, as when dialing a UK number, for example 01234567890 would be written as +441234567890, but if you were calling it, it would be00441234567890 if you're dialing from Europe; however, it would be 011441234567890 if you're in the USA. If they don't always have a country code, how do you know what does, and doesn't? — Thom A
– Thom A ♦, Commented Jun 20, 2019 at 17:47
@Larnu Does every number in your table have a country code? No, some numbers may not have country code. Need to remove country code only if it exists. If it does, is it always consist of 3 digits? No, based on PhoneCodes > DialingPhoneCode for that particular country, we will know how many digits is the dialingphonecode if CountryCode is AUS in PhoneNumbers table then corresponding entry in PhoneCodes will give us DialingPhoneCode is 61 and the code needs to look for number that start with 61 and remove 61 for AUS numbers. — SNew
– SNew, Commented Jun 20, 2019 at 18:13
@Larnu Updated question with sample input and expected output — SNew
– SNew, Commented Jun 20, 2019 at 18:22

Gen Wan · Accepted Answer · 2019-06-21 19:55:25Z

1

You could try this:

SELECT 
    IIF(B.MOBILECODE = DialingPhoneCode, RIGHT(B.MOBILE, LEN(B.MOBILE)-B.CODELENGTH), B.MOBILE) AS UPDATEDMOBILE,
    IIF(B.FAXCODE = DialingPhoneCode, RIGHT(B.FAX, LEN(B.FAX)-B.CODELENGTH), B.FAX) AS UPDATEDFAX,
    IIF(B.LANDLINECODE = DialingPhoneCode, RIGHT(B.LANDLINE, LEN(B.LANDLINE)-B.CODELENGTH), B.LANDLINE) AS UPDATEDLANDLINE,
    B.COUNTRYCODE
FROM (
SELECT A.MOBILE, A.FAX, A.LANDLINE, A.COUNTRYCODE, A.DialingPhoneCode,A.CODELENGTH
,LEFT(A.MOBILE, A.CODELENGTH) AS MOBILECODE
,LEFT(A.Fax, A.CODELENGTH) AS FAXCODE
,LEFT(A.Landline, A.CODELENGTH) AS LANDLINECODE
FROM (
SELECT P1.Mobile,P1.Fax,P1.Landline,P1.CountryCode, P2.DialingPhoneCode
, LEN(P2.DialingPhoneCode) AS CODELENGTH
FROM PhoneNumbers P1
LEFT JOIN PhoneCodes P2
ON P1.CountryCode = P2.CountryCode)A )B

IIF function can only be used in SQL Server 2012 or above.

If you are using the older version, you can use the case statement to replace them.

Test Result (I added two more test samples):

DB<>Fiddle

----Update----

Add Update and case statements:

UPDATE PhoneNumbers
SET Mobile = C.UPDATEDMOBILE,
    Fax = C.UPDATEDFAX,
    Landline = C.UPDATEDLANDLINE
FROM
(
SELECT 
CASE WHEN B.Mobile IS NULL THEN NULL
     WHEN B.MOBILE IS NOT NULL AND B.MOBILECODE = DialingPhoneCode THEN RIGHT(B.MOBILE, LEN(B.MOBILE)-B.CODELENGTH) 
     ELSE B.MOBILE END AS UPDATEDMOBILE,
CASE WHEN B.Fax IS NULL THEN NULL
     WHEN B.FAXCODE = DialingPhoneCode THEN RIGHT(B.FAX, LEN(B.FAX)-B.CODELENGTH) 
     ELSE B.FAX END AS UPDATEDFAX,
CASE WHEN B.Landline IS NULL THEN NULL
     WHEN B.LANDLINECODE = DialingPhoneCode THEN RIGHT(B.LANDLINE, LEN(B.LANDLINE)-B.CODELENGTH) 
     ELSE B.LANDLINE END AS UPDATEDLANDLINE,
    B.COUNTRYCODE, B.Mobile, B.Fax, B.Landline
FROM (
SELECT A.MOBILE, A.FAX, A.LANDLINE, A.COUNTRYCODE, A.DialingPhoneCode,A.CODELENGTH
,LEFT(A.MOBILE, A.CODELENGTH) AS MOBILECODE
,LEFT(A.Fax, A.CODELENGTH) AS FAXCODE
,LEFT(A.Landline, A.CODELENGTH) AS LANDLINECODE
FROM (
SELECT P1.Mobile,P1.Fax,P1.Landline,P1.CountryCode, P2.DialingPhoneCode
, LEN(P2.DialingPhoneCode) AS CODELENGTH
FROM PhoneNumbers P1
LEFT JOIN PhoneCodes P2
ON P1.CountryCode = P2.CountryCode)A )B )C
WHERE   C.Mobile = PhoneNumbers.Mobile 
    AND C.Fax = PhoneNumbers.Fax
    AND C.Landline = PhoneNumbers.Landline

Test Result:

DB<>Fiddle

edited Jun 21, 2019 at 19:55

answered Jun 20, 2019 at 21:37

Gen Wan

2,0192 gold badges15 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

SNew Over a year ago

It is sql server 2008 version, I am looking for update query. Can you please give me the revised query to update PhoneNumbers table that works in sql 2008 version

SNew Over a year ago

Thank you , it worked with sample data and with 100000 records but when I ran against half million records it fails with this error "Invalid length parameter passed to the RIGHT function." It looks some minor thing is failing . Any idea ? FYI some records may or may not have fax and landline.

Gen Wan Over a year ago

The error is because of LEN(B.MOBILE)-B.CODELENGTH must >= 0. Which means you have the number shorter than the country code.

SNew Over a year ago

Thank you and I see the if a record has empty fax it fails. I thought of adding a where clause like where p1.Fax != '' but it will skip the whole record where the record may have valid mobile and landline. Any ideas to handle this case , there is likely case where lot of records will not have fax number or landline number

Gen Wan Over a year ago

Please check if it is [' '] or [''] or [null] then I will add some more case statements.

|

Collectives™ on Stack Overflow

Update Query - T SQL

Sample Data for PhoneNumbers

Sample Data for PhoneCodes

Expected Output for PhoneNumbers

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Sample Data for PhoneNumbers

Sample Data for PhoneCodes

Expected Output for PhoneNumbers

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related