Need some assistance

Hi,
I am importing some phone numbers from a lagacy system and I find some of
them having the following exeptions:
1. 2122121222
2. 212 212 2122
3. 212/212 2122
4. 212/2122122
5. 212.212 2122
6. 212.2122122
7. 212.212.2122
8. 212-212-2122
9. (212) 212-2122
10. 212-2122122
11. (212)-212-2122
12. TEXT
13. 212 2122122
14. 212 -212-2122
15. 212 212 21222
16. 212) 212-2122
17. 2122122122#212
18. 1212 212 2122
How can I format them to (212) 212-2122? I was thinking a function but can
someone assist?
ThanksHi Chris
The code I have supplied below works in a controlled test environment and
may work for you BUT use with extreme caution.
Test the code by using selects before trying the inserts to verify that you
are getting the results required.
You may also like to backup your table before attempting this.
---
drop table #temp
create table #temp(pnumber varchar(50))
insert into #temp values('2122121222')
insert into #temp values('212 212 2122')
insert into #temp values('212/212 2122')
insert into #temp values('212/2122122')
insert into #temp values('212.212 2122')
insert into #temp values('212.2122122')
insert into #temp values('212.212.2122')
insert into #temp values('212-212-2122')
insert into #temp values('(212) 212-2122')
insert into #temp values('212-2122122')
insert into #temp values('(212)-212-2122')
insert into #temp values('TEXT')
insert into #temp values('212 2122122')
insert into #temp values('212 -212-2122')
insert into #temp values('212 212 21222')
insert into #temp values('212) 212-2122')
insert into #temp values('17. 2122122122#212')
insert into #temp values('1212 212 2122')
-- remove know extraneous characters
update #temp
set pnumber = replace(pnumber, ' ', '')
update #temp
set pnumber = replace(pnumber, '.', '')
update #temp
set pnumber = replace(pnumber, '/', '')
update #temp
set pnumber = replace(pnumber, '-', '')
update #temp
set pnumber = replace(pnumber, '(', '')
update #temp
set pnumber = replace(pnumber, ')', '')
update #temp
set pnumber = replace(pnumber, '#', '')
-- set to null where number length is not 10 --
update #temp
set pnumber = null where len(pnumber) <> 10
-- add braces and dash
update #temp
set pnumber = '(' + pnumber
update #temp
set pnumber = left(pnumber, 4) + ') ' + right(pnumber, 7)
update #temp
set pnumber = left(pnumber, 9) + '-' + right(pnumber, 4)
select * from #temp
---
Regards
Peter Hamilton
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:FD7D08A6-B229-4A05-9FDE-DAFAAEEE2E4D@.microsoft.com...
> Hi,
> I am importing some phone numbers from a lagacy system and I find some of
> them having the following exeptions:
> 1. 2122121222
> 2. 212 212 2122
> 3. 212/212 2122
> 4. 212/2122122
> 5. 212.212 2122
> 6. 212.2122122
> 7. 212.212.2122
> 8. 212-212-2122
> 9. (212) 212-2122
> 10. 212-2122122
> 11. (212)-212-2122
> 12. TEXT
> 13. 212 2122122
> 14. 212 -212-2122
> 15. 212 212 21222
> 16. 212) 212-2122
> 17. 2122122122#212
> 18. 1212 212 2122
> How can I format them to (212) 212-2122? I was thinking a function but can
> someone assist?
> Thanks
--== Posted via mcse.ms - Unlimited-Unrestricted-Secure Usenet News=
=--
http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ New
sgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--|||What are you using to import them? Is this in a table? Or a spreadsheet?
Is this a one time thing. Why are all of your phone numbers the same
number! (kidding about the last one :)
If you want to do it in sql (the algorithm would be the same anywhere) just
strip out all of the characters you don't want, leaving you with only
numbers. Then simply put it back together. I would suggest you might want
to put the data in three columns, but certainly a check constraint is
needed. You ought ot get a list of valid area codes to validate against as
swell, if this data is important to you (if it is I would have expected it
would be a bit better taken care of, but you never know.
Note that I just toss off the (likely) extension information, and just using
the 10 characters.
drop table test
go
create table test
(
phoneNumber varchar(15)
)
insert into test
select '2122121222'
union all
select '212 212 2122'
union all
select '212/212 2122'
union all
select '212/2122122'
union all
select '212.212 2122'
union all
select '212.2122122'
union all
select '212.212.2122'
union all
select '212-212-2122'
union all
select '(212) 212-2122'
union all
select '212-2122122'
union all
select '(212)-212-2122'
union all
select 'TEXT'
union all
select '212 2122122'
union all
select '212 -212-2122'
union all
select '212 212 21222'
union all
select '212) 212-2122'
union all
select '2122122122#212'
union all
select '1212 212 2122'
select case when cleaned like '%[a-z]%' then 'INVALID DATA'
when cleaned like '1%' then '(' + substring(cleaned,2,3) + ')' +
substring(cleaned,5,3) + '-' + substring(cleaned,8,4)
else '(' + substring(cleaned,1,3) + ')' + substring(cleaned,4,3) + '-' +
substring(cleaned,7,4) end
--this bit strips out the offensive characters and spaces
from (select
replace(replace(replace(replace(replace(
replace(replace(phoneNumber,'
',''),'(',''),')',''),'-',''),'.',''),'/',''),'#','') as cleaned
from test) as numbers
--
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:FD7D08A6-B229-4A05-9FDE-DAFAAEEE2E4D@.microsoft.com...
> Hi,
> I am importing some phone numbers from a lagacy system and I find some of
> them having the following exeptions:
> 1. 2122121222
> 2. 212 212 2122
> 3. 212/212 2122
> 4. 212/2122122
> 5. 212.212 2122
> 6. 212.2122122
> 7. 212.212.2122
> 8. 212-212-2122
> 9. (212) 212-2122
> 10. 212-2122122
> 11. (212)-212-2122
> 12. TEXT
> 13. 212 2122122
> 14. 212 -212-2122
> 15. 212 212 21222
> 16. 212) 212-2122
> 17. 2122122122#212
> 18. 1212 212 2122
> How can I format them to (212) 212-2122? I was thinking a function but can
> someone assist?
> Thanks|||Probably better to validate this data externally and/or prior to acceptance
into the database. But here's one brute force method.
CREATE TABLE #t
(
phone VARCHAR(32)
);
SET NOCOUNT ON;
INSERT #t SELECT '2122121222';
INSERT #t SELECT '212 212 2122';
INSERT #t SELECT '212/212 2122';
INSERT #t SELECT '212/2122122';
INSERT #t SELECT '212.212 2122';
INSERT #t SELECT '212.2122122';
INSERT #t SELECT '212.212.2122';
INSERT #t SELECT '212-212-2122';
INSERT #t SELECT '(212) 212-2122';
INSERT #t SELECT '212-2122122';
INSERT #t SELECT '(212)-212-2122';
INSERT #t SELECT 'TEXT';
INSERT #t SELECT '212 2122122';
INSERT #t SELECT '212 -212-2122';
INSERT #t SELECT '212 212 21222';
INSERT #t SELECT '212) 212-2122';
INSERT #t SELECT '2122122122#212';
INSERT #t SELECT '1212 212 2122';
UPDATE #t
SET phone =
LTRIM(RTRIM(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(
phone,
-- you may want to add more characters here
' ', ''),
'(', ''),
')', ''),
'#', ''),
'.', ''),
'/', ''),
'-', '')
));
UPDATE #t
SET phone = '('+STUFF(STUFF(phone,4,0,') '),9,0,'-')
WHERE
-- make sure phone has 10 numerical digits
-- you can make this more precise, e.g.
-- disallow 0 in the first position
phone LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]';
SELECT phone FROM #t ORDER BY phone;
DROP TABLE #t;
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:FD7D08A6-B229-4A05-9FDE-DAFAAEEE2E4D@.microsoft.com...
> Hi,
> I am importing some phone numbers from a lagacy system and I find some of
> them having the following exeptions:
> 1. 2122121222
> 2. 212 212 2122
> 3. 212/212 2122
> 4. 212/2122122
> 5. 212.212 2122
> 6. 212.2122122
> 7. 212.212.2122
> 8. 212-212-2122
> 9. (212) 212-2122
> 10. 212-2122122
> 11. (212)-212-2122
> 12. TEXT
> 13. 212 2122122
> 14. 212 -212-2122
> 15. 212 212 21222
> 16. 212) 212-2122
> 17. 2122122122#212
> 18. 1212 212 2122
> How can I format them to (212) 212-2122? I was thinking a function but can
> someone assist?
> Thanks|||For short term, create a scalar UDF like:
CREATE FUNCTION dbo.f ( @.s VARCHAR( 20 ) )
RETURNS VARCHAR( 20 ) AS BEGIN
WHILE PATINDEX( '%[^0-9]%', @.s ) > 0
SET @.s = REPLACE( @.s, SUBSTRING( @.s, PATINDEX( '%[^0-9]%', @.s ), 1 ),
'' )
RETURN CASE WHEN LEN( @.s ) = 10
THEN '(' + STUFF( STUFF( @.s, 4, 0, ') ' ), 9, 0, '-' )
ELSE 'BAD DATA'
END
END
Now try:
SELECT phone_col, dbo.f( phone_col )
FROM tbl ;
Anith|||I am using DTS to import. Thanks for the assistance. I'll try to create a
function that I can add exceptions to in future. I'll be importng this data
on a daily basis from a legacy system which has to validation in the fronten
d
so the users practically enters anything.
"Chris" wrote:

> Hi,
> I am importing some phone numbers from a lagacy system and I find some of
> them having the following exeptions:
> 1. 2122121222
> 2. 212 212 2122
> 3. 212/212 2122
> 4. 212/2122122
> 5. 212.212 2122
> 6. 212.2122122
> 7. 212.212.2122
> 8. 212-212-2122
> 9. (212) 212-2122
> 10. 212-2122122
> 11. (212)-212-2122
> 12. TEXT
> 13. 212 2122122
> 14. 212 -212-2122
> 15. 212 212 21222
> 16. 212) 212-2122
> 17. 2122122122#212
> 18. 1212 212 2122
> How can I format them to (212) 212-2122? I was thinking a function but can
> someone assist?
> Thanks