如果您像我一样，不能仅向生产数据添加函数，但仍然想执行这种过滤，那么这里有一个纯SQL解决方案，使用PIVOT表将过滤后的部分重新组合在一起。

注意:我硬编码表高达40个字符，如果你有更长的字符串要过滤，你将不得不添加更多。

SET CONCAT_NULL_YIELDS_NULL OFF;

with 
    ToBeScrubbed
as (
    select 1 as id, '*SOME 222@ !@* #* BOGUS !@*&! DATA' as ColumnToScrub
),

Scrubbed as (
    select 
        P.Number as ValueOrder,
        isnull ( substring ( t.ColumnToScrub , number , 1 ) , '' ) as ScrubbedValue,
        t.id
    from
        ToBeScrubbed t
        left join master..spt_values P
            on P.number between 1 and len(t.ColumnToScrub)
            and type ='P'
    where
        PatIndex('%[^a-z]%', substring(t.ColumnToScrub,P.number,1) ) = 0
)

SELECT
    id, 
    [1]+ [2]+ [3]+ [4]+ [5]+ [6]+ [7]+ [8] +[9] +[10]
    +  [11]+ [12]+ [13]+ [14]+ [15]+ [16]+ [17]+ [18] +[19] +[20]
    +  [21]+ [22]+ [23]+ [24]+ [25]+ [26]+ [27]+ [28] +[29] +[30]
    +  [31]+ [32]+ [33]+ [34]+ [35]+ [36]+ [37]+ [38] +[39] +[40] as ScrubbedData
FROM (
    select 
        *
    from 
        Scrubbed
    ) 
    src
    PIVOT (
        MAX(ScrubbedValue) FOR ValueOrder IN (
        [1], [2], [3], [4], [5], [6], [7], [8], [9], [10],
        [11], [12], [13], [14], [15], [16], [17], [18], [19], [20],
        [21], [22], [23], [24], [25], [26], [27], [28], [29], [30],
        [31], [32], [33], [34], [35], [36], [37], [38], [39], [40]
        )
    ) pvt

从性能角度来看，我会使用内联函数:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[udf_RemoveNumericCharsFromString]
(
@List NVARCHAR(4000)
)
RETURNS TABLE 
AS RETURN

    WITH GetNums AS (
       SELECT TOP(ISNULL(DATALENGTH(@List), 0))
        n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
        FROM
          (VALUES (0),(0),(0),(0)) d (n),
          (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e (n),
          (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) f (n),
          (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) g (n)
            )

    SELECT StrOut = ''+
        (SELECT Chr
         FROM GetNums
            CROSS APPLY (SELECT SUBSTRING(@List , n,1)) X(Chr)
         WHERE Chr LIKE '%[^0-9]%' 
         ORDER BY N
         FOR XML PATH (''),TYPE).value('.','NVARCHAR(MAX)')


   /*How to Use
   SELECT StrOut FROM dbo.udf_RemoveNumericCharsFromString ('vv45--9gut')
   Result: vv--gut
   */

我刚在Oracle 10g中找到了这个，如果你用的就是它的话。为了进行电话号码比较，我必须去掉所有的特殊字符。

regexp_replace(c.phone, '[^0-9]', '')

CREATE FUNCTION remove_spc_char(@str VARCHAR(MAX))
  RETURNS VARCHAR(MAX) 
AS
BEGIN
  DECLARE @resp    VARCHAR(MAX) = '';
  DECLARE @str_val   VARCHAR(MAX) = UPPER(@str);
  DECLARE @i       INTEGER= 1;
  DECLARE @v_asc   INTEGER;
   WHILE @i <= (LEN(@str_val))
   BEGIN
     SET @v_asc = (ASCII(SUBSTRING(@str_val, @i, 1))) 
        BEGIN
        IF @v_asc in (192,193,194,195,196,65) 
            begin
                SET @v_asc = 65;
                SET @resp = concat(@resp, CHAR(@v_asc));
            end;
        IF @v_asc in (200,201,202,203,233,69)
            begin
                SET @v_asc = 69;
                SET @resp = concat(@resp, CHAR(@v_asc));
            end;
        IF @v_asc in (204,205,206,207,296,73)
            begin
                SET @v_asc = 73;
                SET @resp = concat(@resp, CHAR(@v_asc));
            end;
        IF @v_asc in (210,211,212,213,214,79)
            begin
                SET @v_asc = 79;
                SET @resp = concat(@resp, CHAR(@v_asc));
            end;
        IF @v_asc in (217,218,219,220,85)
            begin
                SET @v_asc = 85;
                SET @resp = concat(@resp, CHAR(@v_asc));
            end;
        IF @v_asc in (199,231,67)
            begin
                SET @v_asc = 67;
                SET @resp = concat(@resp, CHAR(@v_asc));
            end;
        IF @v_asc in (209,78)
            begin
                SET @v_asc = 78;
                SET @resp = concat(@resp, CHAR(@v_asc));
            end;
        IF @v_asc in (924,181,358,216,222,330,272,208,198,42,37,38,34,36,35,
64,33,39,41,40,43,61,95,45,62,60,63,47,176,183,124,166,174,359,248,254,
180,170,186,126,312,331,273,172,178,179,163,162,123,91,93,125,92,167,240,
223,230,171,187,169,185,168)
            begin
                SET @resp = concat(@resp, '');
            end;
        ELSE 
            begin
                if @v_asc not in (65,67,69,73,78,79,85)
                begin
                    SET @resp = concat(@resp, CHAR(@v_asc));
                end;
            end;
        END;
      SET @i = @i + 1
    END;
    RETURN @resp;
END;

这种方式没有为我工作，因为我试图保持阿拉伯字母，我试图取代正则表达式，但它也不起作用。我写了另一个方法工作在ASCII级别，因为这是我唯一的选择，它工作。

 Create function [dbo].[RemoveNonAlphaCharacters] (@s varchar(4000)) returns varchar(4000)
   with schemabinding
begin
   if @s is null
      return null
   declare @s2 varchar(4000)
   set @s2 = ''
   declare @l int
   set @l = len(@s)
   declare @p int
   set @p = 1
   while @p <= @l begin
      declare @c int
      set @c = ascii(substring(@s, @p, 1))
      if @c between 48 and 57 or @c between 65 and 90 or @c between 97 and 122 or @c between 165 and 253 or @c between 32 and 33
         set @s2 = @s2 + char(@c)
      set @p = @p + 1
      end
   if len(@s2) = 0
      return null
   return @s2
   end

GO

我把它放在调用PatIndex的两个地方。

PatIndex('%[^A-Za-z0-9]%', @Temp)

为上面的自定义函数RemoveNonAlphaCharacters并重命名为RemoveNonAlphaNumericCharacters