SQL Server 2000: how do I return only the number from a phone number column

Question

I'm trying to strip out the "(", ")", "-", "x" or "X" "ext" and spaces from each phone number so I am left with only the first 10 digits or 1st 10 numbers of a phone number.

Is there a simple way to allow only the first 10 digits to pass through in sql 2000. I was thinking about using replace but it requires a replace for each character or group of characters and is not very neat. Is there a way with a standard install of sql2000 to return only the first 10 numbers.

Examples before and after

Before                      After
(555) 555-5555 ext55555     5555555555
340 555-5555                3405555555

Answer 1

I think your only option is to create a UDF that does it. If you were using 2005+ you could create a CLR function to do it.

UDF:

create function dbo.RemoveNonNumericChar(@str varchar(500))  
returns varchar(500)  
begin  
declare @startingIndex int  
set @startingIndex=-1 
while @startingIndex <> 0 
begin  
    set @startingIndex= patindex('%[^0-9]%',@str)  
    if @startingIndex <> 0  
    begin  
        set @str = replace(@str,substring(@str,@startingIndex,1),'')  
    end   
end  
return @str  
end

go  

select dbo.RemoveNonNumericChar('(555) 555-5555 ext55555')