从数字+SQL中去除非数字数据 [英] Removing nonnumerical data out of a number + SQL

问题描述

我正在尝试找到从 SQL 中的 varchar 中删除非数字数据的最佳方法，例如

I'm trying find the best way to remove nonnumerical data from a varchar in SQL e.g.

'[phone]' to '0820000000' or
'[phone]' to '0820000000'

困难在于我并不总是确定要采用什么数字格式，如上所示，所以我希望从本质上删除所有不是数字的内容.

The difficulty is i'm not always sure what number formats are coming in, as shown above, so I'd like like everything that is not a number removed essentially.

更新:
根据你们所说的，这是一个小高峰:

Update:
From what you guys have said this is a little spike done:

declare @Num varchar(20)

set @Num = ' + (82) 468 6152 '

--strip nonnumrical data out of @num

print @Num

set @Num = replace(@Num, ' ', '')
set @Num = replace(@Num, '+', '')
set @Num = replace(@Num, '-', '')
set @Num = replace(@Num, '(', '')
set @Num = replace(@Num, ')', '')

print @Num

虽然无法正确替换 [^0-9] 表达式.

Couldn't get the replace [^0-9] expression right though.

推荐答案

如果您使用的是 SQL Server 2005 或更高版本，那么您最好的选择是创建一个 用户定义的 CLR 函数 并使用正则表达式删除所有非数字字符.

If you're using SQL Server 2005 or newer then your best option is to create a user-defined CLR function and use a regular expression to remove all non-numeric characters.

如果您不想使用 CLR 函数，那么您可以创建一个标准的用户定义函数.这将完成这项工作，尽管它不会那么有效:

If you don't want to use a CLR function then you could create a standard user-defined function. This will do the job although it won't be as efficient:

CREATE FUNCTION dbo.RemoveNonNumerics(@in VARCHAR(255))
RETURNS VARCHAR(255)
AS
BEGIN
    DECLARE @out VARCHAR(255)

    IF (@in IS NOT NULL)
    BEGIN
        SET @out = ''

        WHILE (@in <> '')
        BEGIN
            IF (@in LIKE '[0-9]%')
                SET @out = @out + SUBSTRING(@in, 1, 1)

            SET @in = SUBSTRING(@in, 2, LEN(@in) - 1)
        END
    END

    RETURN(@out)
END

然后像这样从你的表格中选择:

And then select from your table like so:

SELECT dbo.RemoveNonNumerics(your_column) AS your_tidy_column
FROM your_table

这篇关于从数字+SQL中去除非数字数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！