试试这个函数:

Create Function [dbo].[RemoveNonAlphaCharacters](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin

    Declare @KeepValues as varchar(50)
    Set @KeepValues = '%[^a-z]%'
    While PatIndex(@KeepValues, @Temp) > 0
        Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')

    Return @Temp
End

这样叫它:

Select dbo.RemoveNonAlphaCharacters('abc1234def5678ghi90jkl')

一旦您理解了代码，您就会发现更改它以删除其他字符也相对简单。您甚至可以使此动态到足以传入您的搜索模式。

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

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'

试试这个函数:

Create Function [dbo].[RemoveNonAlphaCharacters](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin

    Declare @KeepValues as varchar(50)
    Set @KeepValues = '%[^a-z]%'
    While PatIndex(@KeepValues, @Temp) > 0
        Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')

    Return @Temp
End

这样叫它:

Select dbo.RemoveNonAlphaCharacters('abc1234def5678ghi90jkl')

一旦您理解了代码，您就会发现更改它以删除其他字符也相对简单。您甚至可以使此动态到足以传入您的搜索模式。

如果您像我一样，不能仅向生产数据添加函数，但仍然想执行这种过滤，那么这里有一个纯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

SQL Server 2017+的另一个可能的选项，没有循环和/或递归，是使用TRANSLATE()和REPLACE()的基于字符串的方法。

t - sql声明:

DECLARE @pattern varchar(52) = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'

SELECT 
   v.[Text], 
   REPLACE(
      TRANSLATE(
         v.[Text],
         REPLACE(TRANSLATE(v.[Text], @pattern, REPLICATE('a', LEN(@pattern))), 'a', ''),
         REPLICATE('0', LEN(REPLACE(TRANSLATE(v.[Text], @pattern, REPLICATE('a', LEN(@pattern))), 'a', '')))
      ),
      '0',
      ''
   ) AS AlphabeticCharacters
FROM (VALUES
   ('abc1234def5678ghi90jkl#@$&'),
   ('1234567890'),
   ('JAHDBESBN%*#*@*($E*sd55bn')
) v ([Text])

或作为一个函数:

CREATE FUNCTION dbo.RemoveNonAlphabeticCharacters (@Text varchar(1000)) 
RETURNS varchar(1000)
AS BEGIN

   DECLARE @pattern varchar(52) = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'
   SET @text = REPLACE(
      TRANSLATE(
         @Text,
         REPLACE(TRANSLATE(@Text, @pattern, REPLICATE('a', LEN(@pattern))), 'a', ''),
         REPLICATE('0', LEN(REPLACE(TRANSLATE(@Text, @pattern, REPLICATE('a', LEN(@pattern))), 'a', '')))
      ),
      '0',
      ''
   )
   
   RETURN @Text
END