I need help with this query. I am trying to find all the records in our database that have the incorrect phone number format and fix them to the correct format. CREATE TABLE MDR ( SiteName nvarchar(255), BusinessEmailAddress nvarchar(255), FirstName nvarchar(255), LastName nvarchar(255), JobTitle nvarchar(255), PersonBusinessPhoneNumber nvarchar(255), SiteBusinessPhoneNumber nvarchar(255)) INSERT INTO MDR VALUES('ABC Company', 'jlarson@abc.com', 'Jerry', 'Larson', 'HR Director', '+1 (657) 404-8747', '6576488080'),('AAA Company', 'avangelder@aaa.Com', 'Adam', 'Van Gelder', 'IT Director', '+1 (748) 574-5400', '7485745400'),('Puget Sound Company', 'jleaf@puget.com', 'Judy', 'Leaf', 'Sales Manager', '+1 (475) 457-7081', '17067676500'),('Pay Inc', 'dnewton2@payinc.com', 'Danny', 'Newton', 'Sales Director', '4074441448ext', '5857160441'),('New York Company', 'kkordal@nyco.com', 'Keith', 'Kordal', 'CMO', '8008760178ext70', '5857160441'),('New York Company', 'rjones@nyco.com', 'Rick', 'Jones', 'Manager Store Systems', '5854844456ext44', '5857160441'),('New York Company', 'wlyle@nyco.com', 'Will', 'Lyle', 'Phone Support', '5854844456ext44', '5857160441'),('Yale Hospital', 'dani.burch@yhosp.org', 'Dani', 'Burch', 'CTO', '+1 (704) 740-4600', '7046884747'),('State of New York', 'steve_att@newyork.gov', 'Steve', 'Att', 'CIO', '+1 (614) 681-6700', '6147874066'),('Morning Star Inc', 'ajones@morningstar.com', 'Alta', 'Jones', 'MIS Director', '+1 (714) 404-4640', '4176666000'),('Delta Company', 'jwang@deltaco.com', 'Jerry', 'Wang', 'Chief Information Officer', '510/6685100', '5106685100'),('A Tech Company', 'sthomas@techco.com', 'Sunny', 'Thomas', 'Systems Administrator', '6506775464|work', '6506775767'),('Mortgage Corporation', 'dwilliams@mortgage.com', 'Don', 'Williams', 'SVP Sales', '8665665510140', '6775665710'),('Financial Corporation', 'jalberts@fincorp.com', 'Johnny', 'Alberts', 'VP Business Dev', '754/7617800', '7547617800'),('Federal Credit Union', 'nmoney@fcu.org', 'Nicki', 'Money', 'Operations Director', '5184587165x4046', '5184587165');//NOTE: This is query is using our tables within our database.SELECT S.SiteName,P.BusinessEmailAddress,P.FirstName,P.LastName,P.JobTitle,P.BusinessPhoneNumber AS PersonBusinessPhoneNumber,REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(P.BusinessPhoneNumber,'+',''),'(',''),')',''),'-',''),' ','') AS PersonBusinessPhoneNumberFixed,S.BusinessPhoneNumber AS SiteBusinessPhoneNumber,REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(S.BusinessPhoneNumber,'+',''),'(',''),')',''),'-',''),' ','') AS SiteBusinessPhoneNumberFixedFROM MDR.dbo.Person PINNER JOIN MDR.dbo.Site S ON S.SiteGuid = P.SiteGuidWHERE S.BusinessPhoneNumber NOT LIKE REPLICATE('[0-9]',LEN(S.BusinessPhoneNumber)) OR P.BusinessPhoneNumber NOT LIKE REPLICATE('[0-9]',LEN(P.BusinessPhoneNumber))
I am trying to get the output as: PhoneNumber as 10 digitsPersonBusinessPhoneNumber --> PersonBusinessPhoneNumberFixed+1 (657) 404-8747 --> 65740487474074441448ext --> 4074441448510/6685100 --> 51066851006506775464|work --> 65067754648,665,665,510,140 --> This is wrong format entirely so return nothingRS