Sql Server Transact SQL: strip non-numerics from a phone number

[archana.kulkarni]

This Transaction function receives a varchar input and the result is formatted with some rules. Please adapt the procedure.

Since this was run in the state of Victoria, no prefix of (03) meant it was Victoria. Any extensions needed to be preserved, this 12345678 ext 234 had to be returned formatted 03 1234 5678 ext 234.


create function CleanPhone(@instring varchar(100))
returns varchar(max)

begin
declare @ctr int
declare @retstr varchar(100)
declare @length int
declare @char int
declare @char2 int
declare @valchars int
declare @ext varchar(50)
declare @extval varchar(50)
declare @found char(1)
declare @prevch char(1)

set @length = len(@instring)
set @valchars = 0
set @ext = ''

/*
the following section checks to see if there is EXT, EXTEN or EXTENSION in the phone number, if found, it will preserve this
*/

if charindex('EXTENSION',upper(@instring)) > 0
begin
set @ext = substring(@instring,charindex('EXTENSION',upper(@i nstring)), len(@instring) - charindex('EXTENSION',upper(@instring))+ 1)
set @instring = ltrim(rtrim(substring(@instring,1,charindex('EXTEN SION',upper(@instring)) - 1)))
end

if charindex('EXTEN',upper(@instring)) > 0
begin
set @ext = substring(@instring,charindex('EXTEN',upper(@instr ing)), len(@instring) - charindex('EXTEN',upper(@instring))+ 1)
set @instring = ltrim(rtrim(substring(@instring,1,charindex('EXTEN ',upper(@instring)) - 1)))
end

if charindex('EXT',upper(@instring)) > 0
begin
set @ext = substring(@instring,charindex('EXT',upper(@instrin g)), len(@instring) - charindex('EXT',upper(@instring))+ 1)
set @instring = ltrim(rtrim(substring(@instring,1,charindex('EXT', upper(@instring)) - 1)))
end

if @ext != ''
begin
set @found = 'N'
set @char = 1
set @prevch = '~'
set @extval = ''
while ( @char <= len(@ext) )
begin
if substring(@ext,@char,1) = ' ' and @prevch != ' ' and @found = 'Y'
goto GetOutExt -- Armageddon outta here !

if substring(@ext,@char,1) = ' ' and @prevch != ' ' -- current character is ' ', previous is not, set flag
begin
set @found = 'Y'
goto Continue1
end
set @extval = @extval + substring(@ext,@char,1)
Continue1: -- increase the position indicator, set the current character to be the previous character
set @prevch = substring(@ext,@char,1)
set @char = @char + 1
end
end
GetOutExt: -- we are done formatting the Extension now

if ISNUMERIC(@instring) != 1 -- means passed in number is NOT a valid number, so let's strip out the non-numerics
begin
set @char = 0
set @retstr = ''
while @char <= @length
begin
if substring(@instring,@char,1) in ('0','1','2','3','4','5','6','7','8','9')
begin
set @retstr = @retstr + substring(@instring,@char,1)
set @valchars = @valchars + 1
end
set @char = @char + 1
end
set @instring = substring(@retstr,1,10) -- reset the original passed in string with just numeric characters
end

/* Now process the string */

begin
if len(@instring) = 8 -- no area code, assume 03
begin
set @retstr = '03 ' + substring(@instring,1,4) + ' ' + substring(@instring,5,4)
Goto PrepareReturn
end
if substring(@instring,1,2) = '04' -- mobile number, return format 0411 222 333
begin
if len(@instring) = 10 -- full mobile numbers should be 10 charcaters long
begin
set @retstr = substring(@instring,1,4) + ' ' + substring(@instring,5,3) + ' ' + substring(@instring,8,3)
Goto PrepareReturn
end
else -- This is not 8 or 10 characters, so not a mobile number, just return it as is
begin
set @retstr = @instring
Goto PrepareReturn
end
end
else -- not 8 digit length, does not start with 04.
if len(@instring) = 10 -- 10 digit number, just pass it back
set @retstr = substring(@instring,1,2) + ' ' + substring(@instring,3,4) + ' ' + substring(@instring,7,4)
else
if len(@instring) = 8
set @retstr = '03 ' + substring(@instring,3,4) + ' ' + substring(@instring,7,4)
else
set @retstr = @instring
end

PrepareReturn:

if @ext != ''
set @retstr = @retstr + ' ' + @extval

return @retstr

end