SQL Server 2000:如何仅返回电话号码列中的号码 [英] SQL Server 2000: how do I return only the number from a phone number column

问题描述

我试图从每个电话号码中去除("、)"、-"、x"或X"ext"和空格，所以我只剩下前 10 位数字或电话号码的前 10 个号码.

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.

是否有一种简单的方法可以在 sql 2000 中只允许前 10 位数字通过.我正在考虑使用替换，但它需要对每个字符或字符组进行替换，而且不是很整洁.有没有办法标准安装 sql2000 只返回前 10 个数字.

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.

之前和之后的例子

Before                      After
[phone] ext55555     5555555555
340 555-5555                3405555555

推荐答案

我认为您唯一的选择是创建一个执行此操作的 UDF.如果您使用的是 2005+，则可以创建一个 CLR 函数来执行此操作.

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('[phone] ext55555')  

这篇关于SQL Server 2000:如何仅返回电话号码列中的号码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！