Fax/Phone Area code Substring

Question

Post reply

Fax/Phone Area code Substring

getsaby

Old Hand

Points: 339

March 20, 2025 at 7:33 pm

#4564011

Hello,

For the below SQL, why the column "Phone_Country_Code" Code is showing "0" instead of Empty String, tried various logical function but always display 0.

Screenshot 2025-03-20 143110

WITH SM AS 
(SELECT 'US' country_code, '8009222820' order_phone UNION ALL
SELECT 'US','' order_phone UNION ALL
SELECT 'CA', '15403262611' order_phone UNION ALL
SELECT 'CA', null order_phone UNION ALL
SELECT 'CA', ' ' order_phone
)
SELECT 
country_code,order_phone,
case 
when country_code IN ('US','CA') and len(order_phone) = 10 then 1
when country_code IN ('US','CA') and len(order_phone) > 10 and order_phone like '1%' then 1
WHEN order_phone = ' ' OR LEN(order_phone) = 0 THEN ' '
else '' end Phone_Country_Code ,
case 
when country_code IN ('US','CA') and len(order_phone) = 10 then SUBSTRING(order_phone,1,3)
when country_code IN ('US','CA') and len(order_phone) > 10 and order_phone like '1%' then SUBSTRING(order_phone,2,3)
WHEN order_phone = ' ' OR LEN(order_phone) = 0 THEN ' '
else '' end Phone_Country_AREA_Code ,
case 
when country_code IN ('US','CA') and len(order_phone) = 10 then SUBSTRING(order_phone,4,len(order_phone))
when country_code IN ('US','CA') and len(order_phone) > 10 and order_phone like '1%' then SUBSTRING(order_phone,5,len(order_phone))
WHEN order_phone = ' ' OR LEN(order_phone) = 0 THEN ' '
else '' end Phone_Number 
FROM SM

For column order_phone , if it's Blank, NULL, or an Empty String, the value should be an Empty String in column "Phone_Country_Code"

Thanks!

This topic was modified 5 months, 2 weeks ago by getsaby. Reason: Column name

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101200 More actions · Answer 1

Because you used a numeric data type (1) rather than a string. Do this instead:

...
)
SELECT 
country_code,order_phone,
case 
when country_code IN ('US','CA') and len(order_phone) = 10 then '1' --<<--
when country_code IN ('US','CA') and len(order_phone) > 10 and order_phone like '1%' then '1' --<<--
...

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

getsaby Old Hand Points: 339 More actions · Answer 2

getsaby

Old Hand

Points: 339

March 20, 2025 at 8:03 pm

#4564037

Thank you @ScottPletcher, that fix my issue

MMartin1 One Orange Chip Points: 27850 More actions · Answer 3

I do wonder

WHEN order_phone = ' ' , what happens if the value is ' ' (3 spaces) ? The LEN function will still give you zero so that is really all you need.

And not sure if you really need the ' ' (1 space) or just the '' (empty string) as your return value. Just items to think about.

----------------------------------------------------