如何在SQL代替多个字符？(How to Replace Multiple Characters i

这是基于一个类似的问题， 如何在Access SQL替代多个字符？

我写了这一点，因为SQL Server 2005中似乎有一个where子句中的功能上取代的限制（）来替代19。

我有以下任务：需要对列进行匹配，提高匹配的剥离使用替代多个联合国需要字符的机会（）函数

DECLARE @es NVarChar(1) SET @es = ''
DECLARE @p0 NVarChar(1) SET @p0 = '!'
DECLARE @p1 NVarChar(1) SET @p1 = '@'
---etc...

SELECT *
FROM t1,t2 
WHERE  REPLACE(REPLACE(t1.stringkey,@p0, @es), @p1, @es) 
     = REPLACE(REPLACE(t2.stringkey,@p0, @es), @p1, @es)    
---etc 

如果有> 19 REPLACE（）在where子句中，这是行不通的。 于是我想出了解决的办法是在这个例子中创建名为trimChars SQL函数（原谅他们开始@ 22

CREATE FUNCTION [trimChars] (
   @string varchar(max)
) 

RETURNS varchar(max) 
AS
BEGIN

DECLARE @es NVarChar(1) SET @es = ''
DECLARE @p22 NVarChar(1) SET @p22 = '^'
DECLARE @p23 NVarChar(1) SET @p23 = '&'
DECLARE @p24 NVarChar(1) SET @p24 = '*'
DECLARE @p25 NVarChar(1) SET @p25 = '('
DECLARE @p26 NVarChar(1) SET @p26 = '_'
DECLARE @p27 NVarChar(1) SET @p27 = ')'
DECLARE @p28 NVarChar(1) SET @p28 = '`'
DECLARE @p29 NVarChar(1) SET @p29 = '~'
DECLARE @p30 NVarChar(1) SET @p30 = '{'

DECLARE @p31 NVarChar(1) SET @p31 = '}'
DECLARE @p32 NVarChar(1) SET @p32 = ' '
DECLARE @p33 NVarChar(1) SET @p33 = '['
DECLARE @p34 NVarChar(1) SET @p34 = '?'
DECLARE @p35 NVarChar(1) SET @p35 = ']'
DECLARE @p36 NVarChar(1) SET @p36 = '\'
DECLARE @p37 NVarChar(1) SET @p37 = '|'
DECLARE @p38 NVarChar(1) SET @p38 = '<'
DECLARE @p39 NVarChar(1) SET @p39 = '>'
DECLARE @p40 NVarChar(1) SET @p40 = '@'
DECLARE @p41 NVarChar(1) SET @p41 = '-'

return   REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
       @string, @p22, @es), @p23, @es), @p24, @es), @p25, @es), @p26, @es), @p27, @es), @p28, @es), @p29, @es), @p30, @es), @p31, @es), @p32, @es), @p33, @es), @p34, @es), @p35, @es), @p36, @es), @p37, @es), @p38, @es), @p39, @es), @p40, @es), @p41, @es)
END 

这可以那么除了使用其他替换字符串

SELECT *
FROM t1,t2 
WHERE  trimChars(REPLACE(REPLACE(t1.stringkey,@p0, @es), @p1, @es) 
         = REPLACE(REPLACE(t2.stringkey,@p0, @es), @p1, @es))   

我创建了一些更多的功能做类似的更换，像这样trimChars（trimMoreChars（

SELECT *
FROM t1,t2 
WHERE  trimChars(trimMoreChars(REPLACE(REPLACE(t1.stringkey,@p0, @es), @p1, @es) 
         = REPLACE(REPLACE(t2.stringkey,@p0, @es), @p1, @es)))

有人可以给我的表现也许更清洁的实施方面已较好地解决了这个问题？

Answer 1:

我会认真考虑作出CLR UDF 代替 ，并使用正则表达式（包括字符串和模式可以作为参数传入），做一个完整的搜索和替换的字符范围。 它应该很容易超越这个SQL UDF。

Answer 2:

在SQL的一个有用的技巧是利用能力@var = function(...)分配一个值。 如果您在您的记录集中的多条记录，您的变种被分配有副作用多次：

declare @badStrings table (item varchar(50))

INSERT INTO @badStrings(item)
SELECT '>' UNION ALL
SELECT '<' UNION ALL
SELECT '(' UNION ALL
SELECT ')' UNION ALL
SELECT '!' UNION ALL
SELECT '?' UNION ALL
SELECT '@'

declare @testString varchar(100), @newString varchar(100)

set @teststring = 'Juliet ro><0zs my s0x()rz!!?!one!@!@!@!'
set @newString = @testString

SELECT @newString = Replace(@newString, item, '') FROM @badStrings

select @newString -- returns 'Juliet ro0zs my s0xrzone'

Answer 3:

我真的很喜欢@ Juliett的解决方案！ 我只想用一个CTE来获取所有的无效字符：

DECLARE @badStrings VARCHAR(100)
DECLARE @teststring VARCHAR(100)

SET @badStrings = '><()!?@'
SET @teststring = 'Juliet ro><0zs my s0x()rz!!?!one!@!@!@!'

;WITH CTE AS
(
  SELECT SUBSTRING(@badStrings, 1, 1) AS [String], 1 AS [Start], 1 AS [Counter]
  UNION ALL
  SELECT SUBSTRING(@badStrings, [Start] + 1, 1) AS [String], [Start] + 1, [Counter] + 1 
  FROM CTE 
  WHERE [Counter] < LEN(@badStrings)
)

SELECT @teststring = REPLACE(@teststring, CTE.[String], '') FROM CTE

SELECT @teststring

朱丽叶ro0zs我s0xrzone

Answer 4:

我建议你创建一个标量用户定义的函数。 这是一个例子（预先抱歉，因为变量名是西班牙语）：

CREATE FUNCTION [dbo].[Udf_ReplaceChars] (
  @cadena VARCHAR(500),  -- String to manipulate
  @caracteresElim VARCHAR(100),  -- String of characters to be replaced
  @caracteresReem VARCHAR(100)   -- String of characters for replacement
) 
RETURNS VARCHAR(500)
AS
BEGIN
  DECLARE @cadenaFinal VARCHAR(500), @longCad INT, @pos INT, @caracter CHAR(1), @posCarER INT;
  SELECT
    @cadenaFinal = '',
    @longCad = LEN(@cadena),
    @pos = 1;

  IF LEN(@caracteresElim)<>LEN(@caracteresReem)
    BEGIN
      RETURN NULL;
    END

  WHILE @pos <= @longCad
    BEGIN
      SELECT
        @caracter = SUBSTRING(@cadena,@pos,1),
        @pos = @pos + 1,
        @posCarER = CHARINDEX(@caracter,@caracteresElim);

      IF @posCarER <= 0
        BEGIN
          SET @cadenaFinal = @cadenaFinal + @caracter;
        END
      ELSE
        BEGIN
          SET @cadenaFinal = @cadenaFinal + SUBSTRING(@caracteresReem,@posCarER,1)
        END
    END

  RETURN @cadenaFinal;
END

下面是使用该函数的一个例子：

SELECT dbo.Udf_ReplaceChars('This is a test.','sat','Z47');

其结果是：7hiZ IZ 4 7eZ7。

正如你所看到的，每个字符@caracteresElim参数由从同一位置的字符替换@caracteresReem参数。

Answer 5:

declare @testVal varchar(20)

set @testVal = '?t/es?ti/n*g 1*2?3*'

select @testVal = REPLACE(@testVal, item, '') from (select '?' item union select '*' union select '/') list

select @testVal;

Answer 6:

我有一个一次性的数据迁移问题，即源数据不能正确输出一些不寻常/技术特点以及在CSV中无处不在的额外逗号。

我们决定为每个这样的字符源提取物应与东西是可识别的源系统和被加载它们，但它不会是数据，否则SQL Server都替换它们。

然而，它也意味着，在横跨各种表格各列这些替换字符似乎和我将不得不更换它们。 嵌套多个替换功能做出的导入代码看起来吓人的，容易出错的误判支架的位置和数量，所以我写了下面的功能。 我知道它可以处理3000行的表中的列在不到一秒钟，虽然我不知道它将如何迅速扩展到数百万行的表。

create function [dbo].[udf_ReplaceMultipleChars]
(
    @OriginalString nvarchar(4000)
  , @ReplaceTheseChars nvarchar(100)
  , @LengthOfReplacement int = 1
)
returns nvarchar(4000)
begin

    declare @RevisedString nvarchar(4000) = N'';
    declare @lengthofinput int =
            (
            select len(@OriginalString)
            );

with AllNumbers
as (select 1 as  Number
    union all
    select Number + 1
    from AllNumbers
    where Number < @lengthofinput)
select @RevisedString += case
                             when (charindex(substring(@OriginalString, Number, 1), @ReplaceTheseChars, 1) - 1) % 2
    = 0 then
                                 substring(
                                              @ReplaceTheseChars
                                            , charindex(
                                                           substring(@OriginalString, Number, 1)
                                                         , @ReplaceTheseChars
                                                         , 1
                                                       ) + 1
                                            , @LengthOfReplacement
                                          )
                             else
                                 substring(@OriginalString, Number, 1)
                         end
    from AllNumbers
    option (maxrecursion 4000);
    return (@RevisedString);
end;

它的工作原理通过提交了字符串进行评估，并具有字符被替换（@OriginalString）具有成对字符的字符串，其中的第一个字符是由所述第二被替换，第三由第四沿，由第五第六和如此上（@ReplaceTheseChars）。

这里是我需要替换字符及其替代的串... [“]”〜{}Ø°$±|¼|¼ª½¬½^¾＃✓

即，A打开方括号表示一个撇号，关闭一个双引号。 你可以看到，有低俗分数以及度和直径符号在那里。

还有，如果有人需要更换更长的字符串中包含作为起点默认@LengthOfReplacement。 我打得四处在我的项目，但单个字符替换为主要功能。

case语句的条件是很重要的。 它确保如果在您的@ReplaceTheseChars变量，而该角色在奇数位置被发现（从CHARINDEX结果减去1确保任何没有发现返回负模值）发现，只替换字符。 也就是说，如果你发现在位置5波浪号（〜），它会用逗号代替它，但如果在随后的运行它发现在6位逗号不会用花括号代替它（{）。

这可以用一个例子来证明最好...

declare @ProductDescription nvarchar(20) = N'abc~def[¦][123';
select @ProductDescription
= dbo.udf_ReplaceMultipleChars(
                                  @ProductDescription
/* NB the doubling up of the apostrophe is necessary in the string but resolves to a single apostrophe when passed to the function */
                                ,'['']"~,{Ø}°$±|¼¦¼ª½¬½^¾#✓' 
                                , default
                              );
select @ProductDescription
 , dbo.udf_ReplaceMultipleChars(
                                   @ProductDescription
                                 ,'['']"~,{Ø}°$±|¼¦¼ª½¬½^¾#✓'
/* if you didn't know how to type those peculiar chars in then you can build a string like  this... '[' + nchar(0x0027) + ']"~,{' + nchar(0x00D8) + '}' + nchar(0x00B0) etc */
                                ,
                                 default
                               );

这将返回两个第一次通过功能后的值和第二时间如下... ABC，def'¼" “123 ABC，def'¼" ” 123

表更新也只是

update a
set a.Col1 = udf.ReplaceMultipleChars(a.Col1,'~,]"',1)
from TestTable a

最后（我听到你说！），但我已经没有访问翻译功能，我相信这个功能可以处理的文档中表现得相当容易的例子。 的翻译功能演示版

SELECT TRANSLATE('2*[3+4]/{7-2}', '[]{}', '()()');

它返回2×（3 + 4）/（7-2）虽然我明白它可能无法在2 * [3 + 4] / [7-2]工作！

>（{ - - >（我等函数将如下列出每个烧焦被替换，随后它的替换[接近这个

select dbo.udf_ReplaceMultipleChars('2*[3+4]/{7-2}', '[({(])})', 1);

这也将为工作

select dbo.udf_ReplaceMultipleChars('2*[3+4]/[7-2]', '[({(])})', 1);

我希望有人认为这有用的，如果你有机会来测试其对较大的表表现如何呢，让我们知道这样或那样的！

Answer 7:

一种选择是使用数字/理货表通过基于伪集查询，以推动一个反复的过程。

焦炭替代的总体思路可以用一个简单的字符映射表的方法来证明：

create table charMap (srcChar char(1), replaceChar char(1))
insert charMap values ('a', 'z')
insert charMap values ('b', 'y')


create table testChar(srcChar char(1))
insert testChar values ('1')
insert testChar values ('a')
insert testChar values ('2')
insert testChar values ('b')

select 
coalesce(charMap.replaceChar, testChar.srcChar) as charData
from testChar left join charMap on testChar.srcChar = charMap.srcChar

然后你就可以在理货表方法带来做对字符串中的每个字符位置查找。

create table tally (i int)
declare @i int
set @i = 1
while @i <= 256 begin
    insert tally values (@i)
    set @i = @i + 1
end

create table testData (testString char(10))
insert testData values ('123a456')
insert testData values ('123ab456')
insert testData values ('123b456')

select
    i,
    SUBSTRING(testString, i, 1) as srcChar,
    coalesce(charMap.replaceChar, SUBSTRING(testString, i, 1)) as charData
from testData cross join tally
    left join charMap on SUBSTRING(testString, i, 1) = charMap.srcChar
where i <= LEN(testString)

Answer 8:

我不知道为什么查尔斯BRETANA删掉了他的答案，所以我将它添加回为CW的答案，但一个持久化计算列来处理这些情况下，您需要清洁的或转换的数据几乎所有的时间一个非常好的办法，但需要保留原始的垃圾。 他的建议是相关的，适当不管你决定如何清洗你的数据。

具体而言，在我目前的项目，我有带前导零的不一致存储一些特定的数字标识符的持久化计算列其中修剪所有前导零（幸运的是，这是realtively容易直T-SQL处理）。 这被存储在持久性计算列这就需要它和索引，因为该贴合的标识符在连接经常使用的表格中。

Answer 9:

以下是具体步骤

1. 创建一个CLR函数

请参见下面的代码：

public partial class UserDefinedFunctions 
{

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString Replace2(SqlString inputtext, SqlString filter,SqlString      replacewith)
{

    string str = inputtext.ToString();
    try
    {
        string pattern = (string)filter;
        string replacement = (string)replacewith;
        Regex rgx = new Regex(pattern);
        string result = rgx.Replace(str, replacement);
        return (SqlString)result;

    }
    catch (Exception s)
    {
        return (SqlString)s.Message;
    }
}
}

1. 部署您的CLR函数

2. 现在测试一下

请参见下面的代码：

create table dbo.test(dummydata varchar(255))
Go
INSERT INTO dbo.test values('P@ssw1rd'),('This 12is @test')
Go
Update dbo.test
set dummydata=dbo.Replace2(dummydata,'[0-9@]','')

select * from dbo.test
dummydata, Psswrd, This is test booom!!!!!!!!!!!!!

Answer 10:

虽然这个问题被问关于SQL Server 2005，这是值得注意的是，与SQL Server 2017，可以将请求与新的翻译功能来完成。

https://docs.microsoft.com/en-us/sql/t-sql/functions/translate-transact-sql

我希望这些信息可以帮助人们谁去这个页面在未来。

文章来源: How to Replace Multiple Characters in SQL?