在SQL Server中从VARCHAR中删除非数字字符的最快方法

Question

我正在编写一个导入工具，将电话号码作为导入中的唯一键。我需要检查数据库中是否已经存在该电话号码。问题在于，数据库中的电话号码可能带有破折号、括号和其他字符。我编写了一个函数来删除这些字符，但是它很慢，对于我的大量数据来说速度不可接受。我已经将电话号码列设置为索引。

我尝试使用此帖子中的脚本：T-SQL trim &nbsp (and other non-alphanumeric characters) 但是这并没有加快速度。

是否有更快的方法可以删除非数字字符？在比较10,000到100,000条记录时仍能表现良好。

无论采取什么措施，都需要快速执行。

更新： 根据人们的回答，我认为我必须在运行导入工具之前清理字段。

要回答我使用什么语言编写导入工具的问题，它是一个C#应用程序。现在我将BIGINT与BIGINT进行比较，无需修改数据库数据，但即使只有很少的数据（约2000条记录），仍然会出现性能下降的情况。

将BIGINT与BIGINT进行比较是否会减缓速度？

我已经尽可能地优化了应用程序的代码（删除了正则表达式，删除了不必要的数据库调用）。虽然我不能再将SQL隔离为问题的源，但我仍然觉得它是问题所在。

- Dan

Answer 1

我看到了这个使用 T-SQL 代码和 PATINDEX 的解决方案。我很喜欢它 :-)

CREATE Function [fnRemoveNonNumericCharacters](@strText VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
    WHILE PATINDEX('%[^0-9]%', @strText) > 0
    BEGIN
        SET @strText = STUFF(@strText, PATINDEX('%[^0-9]%', @strText), 1, '')
    END
    RETURN @strText
END

- David Coster

Answer 2

replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(string,'a',''),'b',''),'c',''),'d',''),'e',''),'f',''),'g',''),'h',''),'i',''),'j',''),'k',''),'l',''),'m',''),'n',''),'o',''),'p',''),'q',''),'r',''),'s',''),'t',''),'u',''),'v',''),'w',''),'x',''),'y',''),'z',''),'A',''),'B',''),'C',''),'D',''),'E',''),'F',''),'G',''),'H',''),'I',''),'J',''),'K',''),'L',''),'M',''),'N',''),'O',''),'P',''),'Q',''),'R',''),'S',''),'T',''),'U',''),'V',''),'W',''),'X',''),'Y',''),'Z','')*1 AS string，将字符串中的所有字母都替换为空字符串，然后将结果乘以1。

:)

- Brainwater

---

你忘记了 ( ) - # 等等。 - Dave C

太好了！这就是我为什么既喜欢又讨厌 SQL 语言的原因。 - Ken Hadden

嘿，伙计！这是我一生中见过的最实用的解决方案：)) - Abdullah Elen

在现代版本的SQL Server中，有一种更为简洁的替换多个字符的方法。https://dev59.com/T3VD5IYBdhLWcg3wDXF3#74276585 - Martin Smith

Answer 3

如果您不想创建函数，或者只需要在T-SQL中进行单个内联调用，则可以尝试：

set @Phone = REPLACE(REPLACE(REPLACE(REPLACE(@Phone,'(',''),' ',''),'-',''),')','')

当然，这仅适用于删除电话号码格式化，而不是一个通用的从字符串中删除所有特殊字符的函数。

- Tom

Answer 4

我可能误解了，但是您有两组数据需要从其中一个中删除字符串，一个用于当前数据库中的数据，另一个在每次导入时使用。

对于更新现有记录，我会直接使用SQL，这只需要进行一次。

然而，SQL并不针对这种操作进行优化，因为您说您正在编写一个导入工具，我建议在导入工具本身的上下文中执行这些更新，而不是在SQL中执行。从性能角度来看，这将更好。您使用什么语言编写该实用程序？

此外，我可能完全误解了该过程，如果是这样，我很抱歉。

编辑：
对于初始更新，如果您使用的是SQL Server 2005，则可以尝试使用CLR函数。以下是一个使用正则表达式的快速示例。不确定性能如何比较，我自己从未使用过，除了现在进行的快速测试。

using System;  
using System.Data;  
using System.Text.RegularExpressions;  
using System.Data.SqlClient;  
using System.Data.SqlTypes;  
using Microsoft.SqlServer.Server;  

public partial class UserDefinedFunctions  
{  
    [Microsoft.SqlServer.Server.SqlFunction]  
    public static SqlString StripNonNumeric(SqlString input)  
    {  
        Regex regEx = new Regex(@"\D");  
        return regEx.Replace(input.Value, "");  
    }  
};  

如果已经部署完成，您只需要使用以下命令来更新:

UPDATE table SET phoneNumber = dbo.StripNonNumeric(phoneNumber)

- Scott Nichols

Answer 5

简单函数：

CREATE FUNCTION [dbo].[RemoveAlphaCharacters](@InputString VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
  WHILE PATINDEX('%[^0-9]%',@InputString)>0
        SET @InputString = STUFF(@InputString,PATINDEX('%[^0-9]%',@InputString),1,'')     
  RETURN @InputString
END

GO

- AdamE

---

这是一个早期答案的副本，已删除BEGIN和END。遗憾。 - qujck

Answer 6

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

go  

select dbo.RemoveNonNumericChar('aisdfhoiqwei352345234@#$%^$@345345%^@#$^')  

- Debayan Samaddar

Answer 7

从SQL Server 2017开始，原生TRANSLATE函数可用。

如果您有一个已知的要删除的所有字符列表，那么您可以简单地使用以下方法（首先将所有不良字符转换为一个已知的不良字符，然后使用REPLACE去除该特定字符）

DECLARE @BadCharacters VARCHAR(256) = 'abcdefghijklmnopqrstuvwxyz';
        
SELECT REPLACE(
                TRANSLATE(YourColumn, 
                          @BadCharacters, 
                          REPLICATE(LEFT(@BadCharacters,1),LEN(@BadCharacters))),
                LEFT(@BadCharacters,1),
                '')
FROM   @YourTable

如果可能的“坏”字符列表太长而无法提前列举出所有字符，那么您可以使用双重TRANSLATE - Fiddle。

DECLARE @CharactersToKeep VARCHAR(30) = '0123456789'; 
        
SELECT bad_chars, REPLACE(TRANSLATE(YourColumn, bad_chars, REPLICATE(LEFT(bad_chars,1), LEN(bad_chars + 'X') - 1)), LEFT(bad_chars,1), '')
FROM   @YourTable
       CROSS APPLY (SELECT REPLACE(
                                TRANSLATE(YourColumn, 
                                          @CharactersToKeep, 
                                          REPLICATE(LEFT(@CharactersToKeep, 1), LEN(@CharactersToKeep + 'X')-1)), 
                                LEFT(@CharactersToKeep, 1), 
                                '')) ca(bad_chars)

- Martin Smith

Answer 8

我会从性能角度使用内联函数，请参见以下内容：请注意，像“+”、“-”等符号不会被移除。

CREATE FUNCTION [dbo].[UDF_RemoveNumericStringsFromString]
 (
 @str varchar(100)
 )
 RETURNS TABLE AS RETURN
 WITH Tally (n) as 
  (
  -- 100 rows
   SELECT TOP (Len(@Str)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
   FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)
   CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)
  )

  SELECT OutStr =  STUFF(
       (SELECT SUBSTRING(@Str, n,1) st
        FROM Tally
        WHERE ISNUMERIC(SUBSTRING(@Str, n,1)) = 1
        FOR XML PATH(''),type).value('.', 'varchar(100)'),1,0,'')
  GO

  /*Use it*/
  SELECT OutStr
  FROM dbo.UDF_RemoveNumericStringsFromString('fjkfhk759734977fwe9794t23')
  /*Result set
   759734977979423 */

你可以用超过100个字符来定义它...

- hkravitz

---

你能解释一下为什么“+”和“-”不会被移除吗？编辑：算了，这是因为IsNumeric函数。我用这个条件替换了IsNumeric条件：SUBSTRING(@Str, n,1) BETWEEN '0' AND '9'。 - Gabe

Answer 9

你能否在夜间处理中将它们移除并存储到一个单独的字段中，然后在运行该过程之前对已更改的记录进行更新？

或者在插入/更新时，存储“数字”格式以供以后参考。触发器是一种简单的方法。

- Dan Williams

Answer 10

与数字相比，使用varchars基本上是缓慢和低效的，这是显而易见的原因。您在原始帖子中链接的函数确实会很慢，因为它们循环遍历字符串中的每个字符，以确定它是否为数字。对于数千条记录进行此操作，该过程肯定会很慢。这是正则表达式的完美工作，但它们在SQL Server中不受本地支持。您可以使用CLR函数添加支持，但在尝试之前很难说这将有多慢。然而，我肯定希望它比循环遍历每个电话号码的每个字符要快得多！

一旦您在数据库中格式化了电话号码，使其仅为数字，您可以在SQL中切换到数字类型，这将产生与其他数字类型的闪电般快速的比较。您可能会发现，根据新数据的输入速度如何，只要您正在比较的内容格式正确，那么在数据库端进行修剪和转换为数字就足够快了，但如果可能的话，最好编写一个.NET语言的导入实用程序，在命中数据库之前解决这些格式问题。

无论如何，您都将面临一个关于可选格式的大问题。 即使您的数字保证只来自北美，有些人会在完全区号合格的电话号码前面放置1，而其他人则不会，这将导致同一电话号码的多个条目的潜在可能性。 此外，根据您的数据表示方式，有些人将使用他们的家庭电话号码，这可能有几个人住在那里，因此对其进行唯一约束将仅允许每个家庭一个数据库成员。 有些人会使用他们的工作电话号码，并遇到同样的问题，有些人会或不会包括分机号码，这将再次引起人为的唯一性潜力。

所有这些可能或可能不会影响您，具体取决于您的特定数据和用途，但请记住这一点！

- Grank