431/204 split. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

431/204 split.

Author

Topic

mcupryk
Yak Posting Veteran

91 Posts

Posted - 2011-05-19 : 10:11:13

create function [dbo].[FormatPhone](@PhoneNum varchar(50))
returns varchar(50)
as
-- FormatPhone
-- blindman, 3/2004
-- Formats a string of 7-10 numeric values as a phone number, with or without the area code.
begin
-- Test parameters
-- declare @PhoneNum varchar(50)
-- set @PhoneNum = '6142462473'
declare @ReverseNum varchar(50)
set @ReverseNum = Reverse(@PhoneNum)
return reverse(left(@ReverseNum, 4) + '-' + substring(@ReverseNum, 5, 3) + coalesce(' )' + nullif(substring(@ReverseNum, 8, 3), '') + '(', ''))
end

I need to check if area code is there than do nothing else
pad the number with the area code.

There is a new area code being introduce the 431. How should I set this up.

dbo.tblUsers Phone nvarchar(14)
(204) 945-3978

I must look at the different possiblilities
if there is no 204 pad it.

Any help would be awesome.

mcupryk
Yak Posting Veteran

91 Posts

Posted - 2011-05-19 : 10:33:50

(204) 325-4059
204-940-1261
954-7023
(204)642-5759
4537176
here are the way numbers are stored in the datanse

Any help would be awesome.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-19 : 11:25:00

There are probably easier and more elegant/efficient ways to do it, but here is my crude attempt:


;WITH cte1(n,s,f,nums) AS
(
   SELECT   1,
         stuff(v,1,1,''), 
         0,
         cast(CASE WHEN LEFT(v,1) LIKE '[0-9]' THEN LEFT(v,1) ELSE '' END AS VARCHAR(32)) 
   FROM #tmp
   
   UNION ALL
   
   SELECT   n+1,
         stuff(s,1,1,''),
         CASE WHEN LEN(s)= 1 THEN 1 ELSE 0 END,
         cast(nums+CASE WHEN LEFT(s,1) LIKE '[0-9]' THEN LEFT(s,1) ELSE '' END AS VARCHAR(32))
   FROM   cte1 
   WHERE   s <> ''
)
SELECT 
   CASE WHEN nums NOT LIKE '204%' AND LEN(nums) < 10 THEN '204' + nums ELSE nums END AS Num
FROM cte1 WHERE f = 1;

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-19 : 11:29:07

Forgot to post the test data that I was using:

CREATE TABLE #tmp (v VARCHAR(32));
INSERT INTO #tmp VALUES
('(204) 325-4059'),
('204-940-1261'),
('954-7023'),
('(204)642-5759'),
('4537176)');

mcupryk
Yak Posting Veteran

91 Posts

Posted - 2011-05-19 : 12:14:56

I am not sure what u are doing?

Any help would be awesome.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-19 : 12:31:41

I am doing what you described in your initial post, specifically:

I need to check if area code is there than do nothing else
pad the number with the area code.

I must look at the different possiblilities
if there is no 204 pad it.

If you copy the test data and the code and run it, you can see what it is doing.

mcupryk
Yak Posting Veteran

91 Posts

Posted - 2011-05-19 : 12:40:45

CREATE TABLE #tmp (v VARCHAR(32));
INSERT INTO #tmp VALUES
('(204) 325-4059'),--->Incorrect syntax near ','
('204-940-1261'),
('954-7023'),
('(204)642-5759'),
('4537176)');

;WITH cte1(n,s,f,nums) AS
(
SELECT 1,
stuff(v,1,1,''),
0,
cast(CASE WHEN LEFT(v,1) LIKE '[0-9]' THEN LEFT(v,1) ELSE '' END AS VARCHAR(32))
FROM #tmp

UNION ALL

SELECT n+1,
stuff(s,1,1,''),
CASE WHEN LEN(s)= 1 THEN 1 ELSE 0 END,
cast(nums+CASE WHEN LEFT(s,1) LIKE '[0-9]' THEN LEFT(s,1) ELSE '' END AS VARCHAR(32))
FROM cte1
WHERE s <> ''
)
SELECT
CASE WHEN nums NOT LIKE '204%' AND LEN(nums) < 10 THEN '204' + nums ELSE nums END AS Num
FROM cte1 WHERE f = 1;

Any help would be awesome.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-19 : 12:46:20

Ah, sorry about that - SQL 2005 does not let you use multiple values in a single insert. change the insert statement to:


INSERT INTO #tmp VALUES('(204) 325-4059');
INSERT INTO #tmp VALUES('204-940-1261');
INSERT INTO #tmp VALUES('954-7023');
INSERT INTO #tmp VALUES('(204)642-5759');
INSERT INTO #tmp VALUES('4537176)');

mcupryk
Yak Posting Veteran

91 Posts

Posted - 2011-05-19 : 13:25:26

Very good job.

Any help would be awesome.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources