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;

DECLARE @vchVAlue NVARCHAR(255) = 'SWP, Lettering Position 1: 4 Ω, 2: 8 Ω, 3: 16 Ω, 4:  , 5:  , 6:  , Voltage Selector, Solder, 6, Step switch, : w/o fuseholder '


WHILE PATINDEX('%?%' , CAST(@vchVAlue AS VARCHAR(255))) > 0
  BEGIN
    SELECT @vchVAlue = STUFF(@vchVAlue,PATINDEX('%?%' , CAST(@vchVAlue AS VARCHAR(255))),1,' ')
  END 

SELECT @vchVAlue

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

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

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

信不信由你，在我的系统中，这个丑陋的函数比G masters的优雅函数表现得更好。

CREATE FUNCTION dbo.RemoveSpecialChar (@s VARCHAR(256)) 
RETURNS VARCHAR(256) 
WITH SCHEMABINDING
    BEGIN
        IF @s IS NULL
            RETURN NULL
        DECLARE @s2 VARCHAR(256) = '',
                @l INT = LEN(@s),
                @p INT = 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
                    SET @s2 = @s2 + CHAR(@c)
                SET @p = @p + 1
            END

        IF LEN(@s2) = 0
            RETURN NULL

        RETURN @s2

乔治·马斯特罗斯精彩回答的参数化版本:

CREATE FUNCTION [dbo].[fn_StripCharacters]
(
    @String NVARCHAR(MAX), 
    @MatchExpression VARCHAR(255)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    SET @MatchExpression =  '%['+@MatchExpression+']%'
    
    WHILE PatIndex(@MatchExpression, @String) > 0
        SET @String = Stuff(@String, PatIndex(@MatchExpression, @String), 1, '')
    
    RETURN @String
    
END

字母只有:

SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', '^a-z')

数字只有:

SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', '^0-9')

字母数字只有:

SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', '^a-z0-9')

非字母数字：

SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', 'a-z0-9')

SQL Server >= 2017…

declare @text varchar(max)

-- create some sample text
select
@text=
'
Lorem @ipsum  *&dolor-= sit?! amet, {consectetur } adipiscing\ elit. Vivamus commodo justo metus, sed facilisis ante 
congue eget. Proin ac bibendum sem/.
'

-- the characters to be removed
declare @unwanted varchar(max)='''.,!?/<>"[]{}|`~@#$%^&*()-+=/\:;'+char(13)+char(10)

-- interim replaced with
declare @replace_with char(1)=' '

-- call the translate function that will change unwanted characters to spaces
-- in this sample
declare @translated varchar(max)
select @translated=TRANSLATE(@text,@unwanted,REPLICATE(@replace_with,len(@unwanted)))

-- In this case, I want to preserve one space
select  string_agg(trim(value),' ')
from    STRING_SPLIT(@translated,' ')
where   trim(value)<>''

-- Result
'Lorem ipsum dolor sit amet consectetur adipiscing elit Vivamus commodo justo metus sed facilisis ante congue eget Proin ac bibendum sem'