January 18, 2007 at 4:40 pm
I'm trying to manipulate a field (with thousands of rows) that contains telephone numbers so that I can make them diallable....
The field contains stuff like +44 020 7, but sometimes + 44 (0) 207 etc ad infinitum.
If I could clear out all spaces, commas, plus signs etc to just get 440207.... the rest would be easy.
Any ideas?
Thanks very much!
Andy
January 18, 2007 at 8:22 pm
SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Phone,' ',''),',',''),'+',''),'-',''),'(',''),')','')
FROM MyTable
Andy
January 19, 2007 at 2:02 am
Try this script:
declare
@counter smallint
declare
@first varchar(4000)
declare
@notNumber char(1)
while
exists(select Phone
from tabMandu
where ((Phone <> '') and (Phone like '%[^0-9]%')))
begin
set @first = (select top 1 Phone
from tabMandu
where ((Phone <> '') and (Phone like '%[^0-9]%')))
set @counter = 1
while (@counter <= len(@first))
begin
set @notNumber = substring(@first, @counter, 1)
if ((@notNumber = '0')
or (@notNumber = '1')
or (@notNumber = '2')
or (@notNumber = '3')
or (@notNumber = '4')
or (@notNumber = '5')
or (@notNumber = '6')
or (@notNumber = '7')
or (@notNumber = '8')
or (@notNumber = '9'))
begin
set @counter = @counter + 1
end
else
begin
set @counter = len(@first) + 1
end
end
if ((@notNumber <> '0')
and (@notNumber <> '1')
and (@notNumber <> '2')
and (@notNumber <> '3')
and (@notNumber <> '4')
and (@notNumber <> '5')
and (@notNumber <> '6')
and (@notNumber <> '7')
and (@notNumber <> '8')
and (@notNumber <> '9'))
begin
update tabMandu
set Phone = replace(Phone, @notNumber, '')
end
end
print 'End'
January 19, 2007 at 7:12 am
I like the previous scripts, but if you are intent on uses a DTS package, consider the following steps:
1. Create a temp table with a Primary Key, Telephone # & New Telephone #
2. Transform step to temp Table. In the transformation process write custom VB script to remove the offending characters & format the New Telephone #. Remember to keep the original Telephone #.
3. Use a data Driven Query to update your table: Update table set telephone # = New Telephone # where Primary Key = TempTable.Primary Key
The only advantage this approach has is the ability to see before & after telephone #s.
January 19, 2007 at 7:49 am
I created a user defined function to clear out "bad" characters from any column its applied to. You might be able to do something like this:
CREATE FUNCTION DBO.UDFSTRIPCHARS (@INCOMING VARCHAR(2000))
RETURNS VARCHAR(2000)
------------------------------------------------------------------------------------------------------------------------------------------
-- Author: Jeff Georgson
-- Created: 03/20/2006 - 12:15:00
-- Description: User Defined Function - Strips out special characters ( ", !, *, ~, : )from a text parameter passed in
-- Change Log:
-- REV DATE INITIALS COMMENTS
-- 0.1 03/20/06 JG Created udf
------------------------------------------------------------------------------------------------------------------------------------------
AS
BEGIN
DECLARE @OUTGOING VARCHAR(2000)
SET @OUTGOING = REPLACE(@INCOMING, CHAR(34), '')
SET @OUTGOING = REPLACE(@OUTGOING, CHAR(33), '')
SET @OUTGOING = REPLACE(@OUTGOING, CHAR(42), '')
SET @OUTGOING = REPLACE(@OUTGOING, CHAR(126), '')
SET @OUTGOING = REPLACE(@OUTGOING, CHAR(58), '')
RETURN ( @OUTGOING )
END
January 19, 2007 at 11:11 am
before you strip out all characters, note that an autodialer dialing "(800) 555-1212 ext 12"" should dial 18005551212, pause for several seconds, then dial 12. Your autodialer probably has a character it treats as a pause, and any characters x or X should be replaced with a couple of these. Plus, there is the fun of figuring out what phone numbers need a 1 in front. I think the technical term for these two issues is "scope creep" 😉
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply