Fax/Phone Area code Substring
-
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.
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 SMFor 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 2 days, 16 hours ago by getsaby. Reason: Column name
-
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".
-
Thank you @scottpletcher, that fix my issue
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply