如何删除SQL Server中的重音符号和所有字符<>a..z?

你要找的是要去掉的东西 Diacritics 从单个字符。恐怕您所拥有的解决方案将尽可能好,至少在纯SQL中是这样。dotnet/clr没有提供 a simple method 尽管如此。很抱歉,我知道您希望避免使用另一个CLR解决方案,但Microsoft SQL Server没有为此提供等效的T-SQL。

如果你幸运的话,你有 collation 在数据库中设置为“SQL拉丁1_general_cp1_ci_as”或以“SQL拉丁1_general”开头的任何变体。这相当于Windows-1252,非常好 documented . 通过查看每个字符并像以前一样使用SQL case语句映射一个等效字符,您将能够将每个字符“翻译”为英语等效字符。

不过,我对您的代码有一个快速更正。您将要在变量和参数中使用varchar。它产生了执行来回数据类型转换的额外开销,并有可能将仅以Unicode形式存在的Unicode字符引入到混合中。此外,还可以在 Bruce Schneier 的博客。

更新 有关音调符号和Windows国际化的一些重要信息,请参见 Michael S Kaplan 的博客。

实现这一目标的最佳方法是非常简单和高效:

SELECT 'àéêöhello!' Collate SQL_Latin1_General_CP1253_CI_AI

输出“aeeohello!”

字符串不能是Unicode。如果您有一个nvarchar,在使用collate之前将其强制转换为varchar。

这里有一个函数可以满足操作需求:

create function [dbo].[RemoveExtraChars] ( @p_OriginalString varchar(50) )
returns varchar(50) as
begin

  declare @i int = 1;  -- must start from 1, as SubString is 1-based
  declare @OriginalString varchar(100) = @p_OriginalString Collate SQL_Latin1_General_CP1253_CI_AI;
  declare @ModifiedString varchar(100) = '';

  while @i <= Len(@OriginalString)
  begin
    if SubString(@OriginalString, @i, 1) like '[a-Z]'
    begin
      set @ModifiedString = @ModifiedString + SubString(@OriginalString, @i, 1);
    end
    set @i = @i + 1;
  end

  return @ModifiedString

end

然后,命令:

select dbo.RemoveExtraChars('aèàç=.32s df')

输出

aeacsdf

    SELECT 'áéíóú' COLLATE Cyrillic_General_CI_AI

这将替换所有重音字符…

结果:AEIOU

希望这对你有帮助!

让我先澄清一下:您显示的重音字符实际上不是Unicode(正如一个答案所暗示的那样);它们是8位ASCII字符。需要记住的一件事是:您看到类似_¨和_的字符仅仅是因为这是您的代码页(操作系统和/或SQL Server使用的代码页[我不确定是哪一个])显示它们的方式。在不同的代码页中,这些字符将由完全不同的符号表示(例如,如果使用西里尔文或土耳其代码页)。

不管怎样,假设您想用与默认代码页最接近的US/Latin字符等价的字符替换这些8位字符[我假设这些字符来自拉丁字符集的某些变体]。这就是我处理类似问题的方式(免责声明:这不是一个非常优雅的解决方案,但我当时无法想到更好的解决方案):

创建一个UDF,将8位ASCII字符转换为7位可打印的ASCII等效字符,例如:

CREATE FUNCTION dbo.fnCharToAscii
(
  @Char AS VARCHAR
)
RETURNS
  VARCHAR   
AS
BEGIN
IF (@Char IS NULL)
  RETURN ''

-- Process control and DEL chars.
IF (ASCII(@Char) < 32) OR (ASCII(@Char) = 127)
    RETURN ''

-- Return printable 7-bit ASCII chars as is.
-- UPDATE TO DELETE NON-ALPHA CHARS.
IF (ASCII(@Char) >= 32) AND (ASCII(@Char) < 127)
    RETURN @Char

-- Process 8-bit ASCII chars.
RETURN
  CASE ASCII(@Char)
    WHEN 128 THEN 'E'
    WHEN 129 THEN '?'
    WHEN 130 THEN ','
    WHEN 131 THEN 'f'
    WHEN 132 THEN ','
    WHEN 133 THEN '.'
    WHEN 134 THEN '+'
    WHEN 135 THEN '+'
    WHEN 136 THEN '^'
    WHEN 137 THEN '%'
    WHEN 138 THEN 'S'
    WHEN 139 THEN '<'
    WHEN 140 THEN 'C'
    WHEN 141 THEN '?'
    WHEN 142 THEN 'Z'
    WHEN 143 THEN '?'
    WHEN 144 THEN '?'
    WHEN 145 THEN ''''
    WHEN 146 THEN ''''
    WHEN 147 THEN '"'
    WHEN 148 THEN '"'
    WHEN 149 THEN '-'
    WHEN 150 THEN '-'
    WHEN 151 THEN '-'
    WHEN 152 THEN '~'
    WHEN 153 THEN '?'
    WHEN 154 THEN 's'
    WHEN 155 THEN '>'
    WHEN 156 THEN 'o'
    WHEN 157 THEN '?'
    WHEN 158 THEN 'z'
    WHEN 159 THEN 'Y'
    WHEN 160 THEN ' '
    WHEN 161 THEN 'i'
    WHEN 162 THEN 'c'
    WHEN 163 THEN 'L'
    WHEN 164 THEN '?'
    WHEN 165 THEN 'Y'
    WHEN 166 THEN '|'
    WHEN 167 THEN '$'
    WHEN 168 THEN '^'
    WHEN 169 THEN 'c'
    WHEN 170 THEN 'a'
    WHEN 171 THEN '<'
    WHEN 172 THEN '-'
    WHEN 173 THEN '-'
    WHEN 174 THEN 'R'
    WHEN 175 THEN '-'
    WHEN 176 THEN 'o'
    WHEN 177 THEN '+'
    WHEN 178 THEN '2'
    WHEN 179 THEN '3'
    WHEN 180 THEN ''''
    WHEN 181 THEN 'm'
    WHEN 182 THEN 'P'
    WHEN 183 THEN '-'
    WHEN 184 THEN ','
    WHEN 185 THEN '1'
    WHEN 186 THEN '0'
    WHEN 187 THEN '>'
    WHEN 188 THEN '?'
    WHEN 189 THEN '?'
    WHEN 190 THEN '?'
    WHEN 191 THEN '?'
    WHEN 192 THEN 'A'
    WHEN 193 THEN 'A'
    WHEN 194 THEN 'A'
    WHEN 195 THEN 'A'
    WHEN 196 THEN 'A'
    WHEN 197 THEN 'A'
    WHEN 198 THEN 'A'
    WHEN 199 THEN 'C'
    WHEN 200 THEN 'E'
    WHEN 201 THEN 'E'
    WHEN 202 THEN 'E'
    WHEN 203 THEN 'E'
    WHEN 204 THEN 'I'
    WHEN 205 THEN 'I'
    WHEN 206 THEN 'I'
    WHEN 207 THEN 'I'
    WHEN 208 THEN 'D'
    WHEN 209 THEN 'N'
    WHEN 210 THEN 'O'
    WHEN 211 THEN 'O'
    WHEN 212 THEN 'O'
    WHEN 213 THEN 'O'
    WHEN 214 THEN 'O'
    WHEN 215 THEN 'x'
    WHEN 216 THEN 'O'
    WHEN 217 THEN 'U'
    WHEN 218 THEN 'U'
    WHEN 219 THEN 'U'
    WHEN 220 THEN 'U'
    WHEN 221 THEN 'Y'
    WHEN 222 THEN 'b'
    WHEN 223 THEN 'B'
    WHEN 224 THEN 'a'
    WHEN 225 THEN 'a'
    WHEN 226 THEN 'a'
    WHEN 227 THEN 'a'
    WHEN 228 THEN 'a'
    WHEN 229 THEN 'a'
    WHEN 230 THEN 'a'
    WHEN 231 THEN 'c'
    WHEN 232 THEN 'e'
    WHEN 233 THEN 'e'
    WHEN 234 THEN 'e'
    WHEN 235 THEN 'e'
    WHEN 236 THEN 'i'
    WHEN 237 THEN 'i'
    WHEN 238 THEN 'i'
    WHEN 239 THEN 'i'
    WHEN 240 THEN 'o'
    WHEN 241 THEN 'n'
    WHEN 242 THEN 'o'
    WHEN 243 THEN 'o'
    WHEN 244 THEN 'o'
    WHEN 245 THEN 'o'
    WHEN 246 THEN 'o'
    WHEN 247 THEN '-'
    WHEN 248 THEN 'o'
    WHEN 249 THEN 'u'
    WHEN 250 THEN 'u'
    WHEN 251 THEN 'u'
    WHEN 252 THEN 'u'
    WHEN 253 THEN 'y'
    WHEN 254 THEN 'b'
    WHEN 255 THEN 'y'
  END
RETURN ''
END

上面的代码是通用的,因此您可以调整字符映射以删除所有非字母字符,例如,您可以在可打印的7位ASCII字符的匹配中使用类似这样的代码(假定不区分大小写的排序规则):

IF @Char NOT LIKE '[a-z]' RETURN ''

要查看8位ASCII符号的字符映射是否正常工作,请运行以下代码:

DECLARE @I   INT
DECLARE @Msg VARCHAR(32)

SET @I = 128

WHILE @I < 256
BEGIN
    SELECT @Msg = CAST(@I AS VARCHAR) + 
    ': ' + 
    CHAR(@I) + 
    '=' + 
    dbo.fnCharToAscii(CHAR(@I))
    PRINT @Msg
    SET @I = @I + 1 
END

现在,您可以创建一个UDF来处理字符串:

CREATE FUNCTION dbo.fnStringToAscii
(
  @Value AS VARCHAR(8000)
)
RETURNS
  VARCHAR(8000) 
AS
BEGIN
IF (@Value IS NULL OR DATALENGTH(@Value) = 0)
  RETURN ''

DECLARE @Index  INT
DECLARE @Result VARCHAR(8000)

SET @Result = ''
SET @Index  = 1

WHILE (@Index <= DATALENGTH(@Value))
BEGIN
  SET @Result = @Result + dbo.fnCharToAscii(SUBSTRING(@Value, @Index, 1))
  SET @Index = @Index + 1   
END

RETURN @Result
END
GO

你可以避免硬编码 REPLACE 语句使用 COLLATE 带有不区分重音排序规则的子句,用于比较重音字母字符与非字母字符:

DECLARE 
  @s1 NVARCHAR(200),
  @s2 NVARCHAR(200)

SET @s1 = N'aèàç=.32s df' 

SET @s2 = N''
SELECT @s2 = @s2 + no_accent 
FROM ( 
  SELECT 
    SUBSTRING(@s1, number, 1) AS accent,
    number
  FROM master.dbo.spt_values 
  WHERE TYPE = 'P'
  AND number BETWEEN 1 AND LEN(@s1) 
) s1 
INNER JOIN (
  SELECT NCHAR(number) AS no_accent
  FROM master.dbo.spt_values 
  WHERE type = 'P'
  AND (number BETWEEN 65 AND 90 OR number BETWEEN 97 AND 122) 
) s2 
  ON s1.accent COLLATE LATIN1_GENERAL_CS_AI = s2.no_accent 
ORDER BY number

SELECT @s1 
SELECT @s2 

/*
aèàç=.32s df
aeacsdf
*/

对于Unicode/UTF-X字符,没有直接映射“看起来类似”。除非有人有更酷的东西,否则我建议你采取蛮力的方法,这样你才能在那之前完成你的工作。

听起来你需要做两次传球。第一个步骤是先替换外观相似的字母,然后再遍历并删除所有剩余的非英语字母。

本文可以帮助您创建一个用户定义的函数,这样您就可以使用正则表达式而不是几十个替换调用: http://msdn.microsoft.com/en-us/magazine/cc163473.aspx

这是我为这个案子使用的字典:

    public static Dictionary<char, string> NonEnglishLetterMapping = new Dictionary<char, string>
    {
          {'a', "áàâãäåāăą"}
        //, {'b', ""}
        , {'c', "ćĉċč"}
        , {'d', "ďđ"}
        , {'e', "éëêèēĕėę"}
        //, {'f', ""}
        , {'g', "ĝğġģ"}
        , {'h', "ĥħ"}
        , {'i', "ìíîïĩīĭįı"}
        , {'j', "ĵ"}
        , {'k', "ķĸ"}
        , {'l', "ĺļľŀł"}
        //, {'m', ""}
        , {'n', "ñńņňʼnŋ"}
        , {'o', "òóôõöōŏőơ"}
        //, {'p', ""}
        //, {'q', ""}
        , {'r', "ŕŗř"}
        , {'s', "śŝşšș"}
        , {'t', "ţťŧț"}
        , {'u', "ùúûüũūŭůűųư"}
        //, {'v', ""}
        , {'w', "ŵ"}
        //, {'x', ""}
        , {'y', "Å·"}
        , {'z', "źżž"}
    };

嗯,这不是更好,但至少是TSQL集解决方案

declare @TempString varchar(100)

set @TempString='textàè containing éìòaccentsç''''` and things-'

select @TempString=
    replace(
        replace(
            replace(
                replace(
                    replace(
                        replace(
                            replace(
                                replace(
                                    replace(
                                        replace(
                                            replace(@TempString,' ', '') 
                                        ,'à', 'a')
                                    ,'è', 'e') 
                                ,'é', 'e')
                            ,'ì', 'i')
                        ,'ò', 'o') 
                    ,'ù', 'u') 
                ,'ç', 'c') 
            ,'''', '') 
        ,'`', '')
    ,'-', '') 



select @TempString

在您的模式中有两个辅助表,您可以使用 SELECT 声明。

首先声明一个视图来实现一个数字表,从1到65536。以下技术是由于 Itzik Ben-Gan :

CREATE VIEW Sequence
AS
WITH T1(_) AS (SELECT NULL UNION ALL SELECT NULL),
T2(_) AS (SELECT NULL FROM T1 AS L CROSS JOIN T1 AS R),
T3(_) AS (SELECT NULL FROM T2 AS L CROSS JOIN T2 AS R),
T4(_) AS (SELECT NULL FROM T3 AS L CROSS JOIN T3 AS R),
T5(_) AS (SELECT NULL FROM T4 AS L CROSS JOIN T4 AS R)
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Number
FROM T5;

接下来声明一个带有音调符号的字符和它们的非音调等位词之间的映射。此示例数据不是完整的映射,但用作示例:

CREATE TABLE UndiacriticMap (
  DiacriticCharacter CHAR(1) NOT NULL PRIMARY KEY,
  UndiacriticCharacter CHAR(1) NOT NULL
);

INSERT INTO UndiacriticMap (
  DiacriticCharacter,
  UndiacriticCharacter
)
VALUES
  ('à', 'a'),
  ('è', 'e'),
  ('é', 'e'),
  ('ì', 'i'),
  ('ò', 'o'),
  ('ç', 'c');

最后声明一个包含测试数据的列的表。数据来源于问题和 DForck42's answer :

CREATE TABLE TestData (
  ID INT NOT NULL PRIMARY KEY,
  String VARCHAR(50) NOT NULL
);

INSERT INTO TestData (
  ID,
  String
)
VALUES
  (1, 'textàè containing éìòaccentsç''''` and things-'),
  (2, 'aèàç=.32s df');

在这些对象就位后,以下 选择 语句以关系的方式处理测试数据,保存为非关系黑客将字符重新连接在一起。“blackbox xml方法”是由于 Anith Sen :

WITH CharacterWise (ID, Symbol, Position) AS (
  SELECT
    ID,
    SUBSTRING(TestData.String, Sequence.Number, 1),
    Sequence.Number
  FROM TestData
  INNER JOIN Sequence ON
    Sequence.Number <= LEN(TestData.String)
),
Stripped (ID, Symbol, Position) AS (
  SELECT
    ID,
    CASE 
      WHEN UndiacriticMap.DiacriticCharacter IS NOT NULL
      THEN UndiacriticMap.UndiacriticCharacter
      ELSE CASE 
        WHEN CharacterWise.Symbol LIKE '[a-z]' COLLATE Latin1_General_BIN
        THEN CharacterWise.Symbol
        ELSE ''
      END
    END,
    CharacterWise.Position
  FROM CharacterWise
  LEFT OUTER JOIN UndiacriticMap ON
    UndiacriticMap.DiacriticCharacter = CharacterWise.Symbol
)
SELECT
  TestData.ID,
  TestData.String,
  (
    SELECT Stripped.Symbol AS [text()]
    FROM Stripped
    WHERE TestData.ID = Stripped.ID
    ORDER BY Stripped.Position
    FOR XML PATH('')
  ) AS StrippedString
FROM TestData;

查询生成以下结果集:

ID          String                                             StrippedString
----------- -------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           textàè containing éìòaccentsç''` and things-       textaecontainingeioaccentscandthings
2           aèàç=.32s df                                       aeacsdf

我知道这并不优雅,但是当collate不起作用并且您不能使用函数时,您可以使用这个嵌套的替换来替换大多数使用的音调符号。我把这个贴出来,这样你就不用自己再打了:)

select 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(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(myfield,'é','e'),'ê','e'),'ë','e'),'è','e'),'É','E'),'È','E'),'Ê','E'),'Ë','E'),'ð','D'),'Ð','D'),'â','a'),'à','a'),'á','a'),'ã','a'),'æ','a'),'à','a'),'å','a'),'Å','A'),'À','A'),'Á','A'),'Â','A'),'Ã','A'),'Æ','A'),'ä','a'),'Ä','A'),'ï','i'),'î','i'),'ì','i'),'í','i'),'Ì','I'),'Í','I'),'Î','I'),'Ï','I'),'ô','o'),'ò','o'),'ó','o'),'õ','o'),'ø','o'),'Ò','O'),'Ó','O'),'Ô','O'),'Õ','O'),'Ø','O'),'ö','o'),'Ö','O'),'û','u'),'ù','u'),'ú','u'),'Ù','U'),'Ú','U'),'Û','U'),'Ü','U'),'ü','u'),'ñ','n'),'Ñ','N'),'Ç','C'),'ç','c'),'ý','y'),'ÿ','y'),'Ý','Y'),'þ','T'),'Þ','t'),'ß','ss') from mytable

离我只有两美分

select * From XXX  
    where cast(word as varchar(max)) collate SQL_Latin1_General_CP1253_CI_AI = 'sluiten' collate SQL_Latin1_General_CP1253_CI_AI

在Postgress 10中:

create extension unaccent;# as root in Your db, for each db
select unaccent("ąęśłóŻŹŁÓĄĘ");

:)

select * from database_name.table_name 
where countries LIKE '%é%'
or countries like'%é%'
or countries like'%ê%'
or countries like'%ë%'
or countries like'%è%'
or countries like'%É%'
or countries like'%È%'
or countries like'%Ê%'
or countries like'%Ë%'
or countries like'%ð%'
or countries like'%Ð%'
or countries like'%â%'
or countries like'%à%'
or countries like'%á%'
or countries like'%ã%'
or countries like'%æ%'
or countries like'%à%'
or countries like'%Ã¥%'
or countries like'%Å%'
or countries like'%À%'
or countries like'%Á%'
or countries like'%Â%'
or countries like'%Ã%'
or countries like'%Æ%'
or countries like'%ä%'
or countries like'%Ä%'
or countries like'%ï%'
or countries like'%î%'
or countries like'%ì%'
or countries like'%í%'
or countries like'%Ì%'
or countries like'%Í%'
or countries like'%Î%'
or countries like'%Ï%'
or countries like'%ô%'
or countries like'%ò%'
or countries like'%ó%'
or countries like'%õ%'
or countries like'%ø%'
or countries like'%Ò%'
or countries like'%Ó%'
or countries like'%Ô%'
or countries like'%Õ%'
or countries like'%Ø%'
or countries like'%ö%'
or countries like'%Ö%'
or countries like'%û%'
or countries like'%ù%'
or countries like'%ú%'
or countries like'%Ù%'
or countries like'%Ú%'
or countries like'%Û%'
or countries like'%Ü%'
or countries like'%ü%'
or countries like'%ñ%'
or countries like'%Ñ%'
or countries like'%Ç%'
or countries like'%ç%'
or countries like'%ý%'
or countries like'%ÿ%'
or countries like'%Ý%'
or countries like'%þ%'
or countries like'%Þ%'
or countries like'%ß%';