0

I am currently trying to run an SQL qeuery that strips out special characters and then adds a '1' in front to have all numbers correctly formatted. For what ever reason my REPLACE doesn't seem to work as I get return values like this 1(111) 111- after the query completes. I am not getting any error and most of the numbers are in the correct fomrat just a random chunk that won't remove the characters. Here is my Query:

SELECT 
DISTINCT 
CASE
 WHEN 
 LEFT(REPLACE(REPLACE(REPLACE(REPLACE(Phone,'(',''),')',''),' ',''),'-',''),1) <> 1 THEN CAST('1' AS VARCHAR(1)) + CAST(Phone AS VARCHAR(10))
 ELSE Phone
END AS [Mobile Number],
id, PersonEmail, Phone, Name, Status__pc, User_Type__c, 'US' AS Locale
FROM Account_Salesforce
WHERE Status__pc = 'Active'
AND User_Type__c = 'Agent'
AND id IS NOT NULL
1

I have figured it out! Here is the correct query:

SELECT scrubbed_phone, id, PersonEmail, Phone, Name, Status__pc, User_Type__c, Locale,
CASE
 WHEN 
 LEFT(scrubbed_phone,1) <> '1' THEN CONCAT('1',scrubbed_phone)
 ELSE scrubbed_phone
END AS [Mobile Number]
FROM
(
SELECT 
DISTINCT 
REPLACE(REPLACE(REPLACE(REPLACE(Phone,'(',''),')',''),' ',''),'-','') AS scrubbed_phone,
id, PersonEmail, Phone, Name, Status__pc, User_Type__c, 'US' AS Locale
FROM Account_Salesforce
WHERE Status__pc = 'Active'
AND User_Type__c = 'Agent'
AND id IS NOT NULL) AS asdf

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.