如何从字符串中删除所有非字母的字符?

非字母数字呢?

这必须是一个自定义函数还是也有更通用的解决方案?


当前回答

看过所有给出的解决方案后,我认为必须有一个纯SQL方法,它不需要函数或CTE / XML查询,并且不涉及难以维护的嵌套REPLACE语句。以下是我的解决方案:

SELECT 
  x
  ,CASE WHEN a NOT LIKE '%' + SUBSTRING(x, 1, 1) + '%' THEN '' ELSE SUBSTRING(x, 1, 1) END
    + CASE WHEN a NOT LIKE '%' + SUBSTRING(x, 2, 1) + '%' THEN '' ELSE SUBSTRING(x, 2, 1) END
    + CASE WHEN a NOT LIKE '%' + SUBSTRING(x, 3, 1) + '%' THEN '' ELSE SUBSTRING(x, 3, 1) END
    + CASE WHEN a NOT LIKE '%' + SUBSTRING(x, 4, 1) + '%' THEN '' ELSE SUBSTRING(x, 4, 1) END
    + CASE WHEN a NOT LIKE '%' + SUBSTRING(x, 5, 1) + '%' THEN '' ELSE SUBSTRING(x, 5, 1) END
    + CASE WHEN a NOT LIKE '%' + SUBSTRING(x, 6, 1) + '%' THEN '' ELSE SUBSTRING(x, 6, 1) END
-- Keep adding rows until you reach the column size 
    AS stripped_column
FROM (SELECT 
        column_to_strip AS x
        ,'ABCDEFGHIJKLMNOPQRSTUVWXYZ' AS a 
      FROM my_table) a

这样做的好处是,有效字符包含在子查询中的一个字符串中,便于为不同的字符集重新配置。

缺点是您必须为每个字符添加一行SQL,直到您的列的大小。为了让这个任务更容易,我只是使用了下面的Powershell脚本,这个例子如果是VARCHAR(64):

1..64 | % {
  "    + CASE WHEN a NOT LIKE '%' + SUBSTRING(x, {0}, 1) + '%' THEN '' ELSE SUBSTRING(x, {0}, 1) END" -f $_
} | clip.exe

其他回答

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不擅长字符串操作,但我没想到它会这么难。下面是一个简单的函数,用于从字符串中剥离所有数字。当然还有更好的办法,但这只是个开始。

CREATE FUNCTION dbo.AlphaOnly (
    @String varchar(100)
)
RETURNS varchar(100)
AS BEGIN
  RETURN (
    REPLACE(
      REPLACE(
        REPLACE(
          REPLACE(
            REPLACE(
              REPLACE(
                REPLACE(
                  REPLACE(
                    REPLACE(
                      REPLACE(
                        @String,
                      '9', ''),
                    '8', ''),
                  '7', ''),
                '6', ''),
              '5', ''),
            '4', ''),
          '3', ''),
        '2', ''),
      '1', ''),
    '0', '')
  )
END
GO

-- ==================
DECLARE @t TABLE (
    ColID       int,
    ColString   varchar(50)
)

INSERT INTO @t VALUES (1, 'abc1234567890')

SELECT ColID, ColString, dbo.AlphaOnly(ColString)
FROM @t

输出

ColID ColString
----- ------------- ---
    1 abc1234567890 abc

第2轮-数据驱动黑名单

-- ============================================
-- Create a table of blacklist characters
-- ============================================
IF EXISTS (SELECT * FROM sys.tables WHERE [object_id] = OBJECT_ID('dbo.CharacterBlacklist'))
  DROP TABLE dbo.CharacterBlacklist
GO
CREATE TABLE dbo.CharacterBlacklist (
    CharID              int         IDENTITY,
    DisallowedCharacter nchar(1)    NOT NULL
)
GO
INSERT INTO dbo.CharacterBlacklist (DisallowedCharacter) VALUES (N'0')
INSERT INTO dbo.CharacterBlacklist (DisallowedCharacter) VALUES (N'1')
INSERT INTO dbo.CharacterBlacklist (DisallowedCharacter) VALUES (N'2')
INSERT INTO dbo.CharacterBlacklist (DisallowedCharacter) VALUES (N'3')
INSERT INTO dbo.CharacterBlacklist (DisallowedCharacter) VALUES (N'4')
INSERT INTO dbo.CharacterBlacklist (DisallowedCharacter) VALUES (N'5')
INSERT INTO dbo.CharacterBlacklist (DisallowedCharacter) VALUES (N'6')
INSERT INTO dbo.CharacterBlacklist (DisallowedCharacter) VALUES (N'7')
INSERT INTO dbo.CharacterBlacklist (DisallowedCharacter) VALUES (N'8')
INSERT INTO dbo.CharacterBlacklist (DisallowedCharacter) VALUES (N'9')
GO

-- ====================================
IF EXISTS (SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID('dbo.StripBlacklistCharacters'))
  DROP FUNCTION dbo.StripBlacklistCharacters
GO
CREATE FUNCTION dbo.StripBlacklistCharacters (
    @String nvarchar(100)
)
RETURNS varchar(100)
AS BEGIN
  DECLARE @blacklistCt  int
  DECLARE @ct           int
  DECLARE @c            nchar(1)

  SELECT @blacklistCt = COUNT(*) FROM dbo.CharacterBlacklist

  SET @ct = 0
  WHILE @ct < @blacklistCt BEGIN
    SET @ct = @ct + 1

    SELECT @String = REPLACE(@String, DisallowedCharacter, N'')
    FROM dbo.CharacterBlacklist
    WHERE CharID = @ct
  END

  RETURN (@String)
END
GO

-- ====================================
DECLARE @s  nvarchar(24)
SET @s = N'abc1234def5678ghi90jkl'

SELECT
    @s                  AS OriginalString,
    dbo.StripBlacklistCharacters(@s)   AS ResultString

输出

OriginalString           ResultString
------------------------ ------------
abc1234def5678ghi90jkl   abcdefghijkl

我对读者的挑战是:你能让这个过程更有效率吗?那么使用递归呢?

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

试试这个函数:

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方法,它不需要函数或CTE / XML查询,并且不涉及难以维护的嵌套REPLACE语句。以下是我的解决方案:

SELECT 
  x
  ,CASE WHEN a NOT LIKE '%' + SUBSTRING(x, 1, 1) + '%' THEN '' ELSE SUBSTRING(x, 1, 1) END
    + CASE WHEN a NOT LIKE '%' + SUBSTRING(x, 2, 1) + '%' THEN '' ELSE SUBSTRING(x, 2, 1) END
    + CASE WHEN a NOT LIKE '%' + SUBSTRING(x, 3, 1) + '%' THEN '' ELSE SUBSTRING(x, 3, 1) END
    + CASE WHEN a NOT LIKE '%' + SUBSTRING(x, 4, 1) + '%' THEN '' ELSE SUBSTRING(x, 4, 1) END
    + CASE WHEN a NOT LIKE '%' + SUBSTRING(x, 5, 1) + '%' THEN '' ELSE SUBSTRING(x, 5, 1) END
    + CASE WHEN a NOT LIKE '%' + SUBSTRING(x, 6, 1) + '%' THEN '' ELSE SUBSTRING(x, 6, 1) END
-- Keep adding rows until you reach the column size 
    AS stripped_column
FROM (SELECT 
        column_to_strip AS x
        ,'ABCDEFGHIJKLMNOPQRSTUVWXYZ' AS a 
      FROM my_table) a

这样做的好处是,有效字符包含在子查询中的一个字符串中,便于为不同的字符集重新配置。

缺点是您必须为每个字符添加一行SQL,直到您的列的大小。为了让这个任务更容易,我只是使用了下面的Powershell脚本,这个例子如果是VARCHAR(64):

1..64 | % {
  "    + CASE WHEN a NOT LIKE '%' + SUBSTRING(x, {0}, 1) + '%' THEN '' ELSE SUBSTRING(x, {0}, 1) END" -f $_
} | clip.exe