SQL - 替换所有“ASCII /特殊字符”在字符串中 [英] SQL - Replacing all "ASCII/special characters" in a string

问题描述

编辑：我有大约80个字符，导致我的应用程序的问题，所以我不想硬编码REPLACE为每个单个字符。我认为创建一个具有两个列，特殊字符和替换字符的单独的表将更容易，我将删除包含StringTest列的原始表中的那些列。我的目标是弄清楚如何使用字符表来替换字符串表中的字符。

I have about 80 characters that are causing problems in my application so I don't want to hard code a REPLACE for every single character. I think it would be easier to create a separate table with two columns,"special characters" and "replacement characters", and I will remove those columns from the original table which contains the column "StringTest". My goal will be figuring out how to use the characters table to replace characters in the string table.

我试图替换所有的特殊字符（即À， ，Ç）在SQL Server中使用MappedCharacters（A，AE，C）。我已经尝试了两种不同的技术，一种使用光标，一种没有光标，搜索字符串和替换所有特殊字符映射字符。我的每个方法只替换与字符串在同一行的字符。
之前的示例：

I am trying to replace all "special characters" (ie À, Æ, Ç) with "MappedCharacters" (A, AE, C) in SQL Server. I have tried two different techniques, one using a cursor, one without a cursor, to search through a string and replace all special characters with mapped characters. Each of my methods only replaces characters they are in the same row as the string. Example before:

num   SpecialCharacter    MappedCharacter    StringTest
 1           À                   A             StringÀÆ
 2           Æ                   AE            ÆStringÆ
 3           Ç                   C             StrÇÀing

后面的示例：

num   SpecialCharacter    MappedCharacter    StringTest
 1           À                   A             StringAÆ
 2           Æ                   AE            AEStringAE
 3           Ç                   C             StrCÀing

首选输出：

num   SpecialCharacter    MappedCharacter    StringTest
 1           À                   A             StringAAE
 2           Æ                   AE            AEStringAE
 3           Ç                   C             StrCAing

b $ b

所以你可以看到，我想替换StringTest中的所有特殊字符，但只有在同一行中的字符正在替换。

So you can see that I want to replace all "special characters" in StringTest but only characters that are in the same row are getting replaced.

我还没有弄清楚如何做到这一点。

I haven't quite figured out how to do that just yet.

这里是我一直在尝试修改的两个SQL代码（我只需要一个工作）

Here are the two SQL code that I have been trying to modify (I only need one to work)

第一种方法：

              DECLARE @cASCIINum INT;
              DECLARE @cSpecialChar VARCHAR(50);
              DECLARE @cMappedChar VARCHAR(50);
              DECLARE @cStringTest VARCHAR(50);

              DECLARE @mapCursor as CURSOR;

              SET @mapCursor = CURSOR FOR
              SELECT [ASCIINum]
                    ,[SpecialChar]
                    ,[MappedChar]
                    ,[StringTest]
              FROM [intranet].[dbo].[CharMapTestTab]; 

              OPEN @mapCursor;
              FETCH NEXT FROM @mapCursor INTO @cASCIINum,
                                              @cSpecialChar,
                                              @cMappedChar,
                                              @cStringTest;

                WHILE @@FETCH_STATUS = 0
                BEGIN

                  UPDATE [intranet].[dbo].[CharMapTestTab]
                  SET StringTest = REPLACE(StringTest, SpecialChar, MappedChar)
                  WHERE SpecialChar <> MappedChar

                END

                CLOSE @mapCursor;
                DEALLOCATE @mapCursor;

第二种方法：

            DECLARE @ASCIINum INT = 0

            WHILE (1 = 1) 
            BEGIN  

              SELECT @ASCIINum = ASCIINum
              FROM [intranet].[dbo].[CharMapTestTab]
              WHERE ASCIINum > @ASCIINum 
              ORDER BY ASCIINum

              IF @@ROWCOUNT = 0 BREAK;

                  UPDATE [intranet].[dbo].[CharMapTestTab]
                  SET StringTest = REPLACE(StringTest, SpecialChar, MappedChar)
                  WHERE SpecialChar <> MappedChar


              SELECT TOP 1000 [ASCIINum]
                  ,[SpecialChar]
                  ,[MappedChar]
                  ,[StringTest]
              FROM [intranet].[dbo].[CharMapTestTab]



            END

推荐答案

尝试这样，它比循环更好，因为只有一个更新：

Try this, it works better than looping because there is only 1 update:

-- create test table vc
create table vc(StringTest varchar(20))
insert vc values('StringÀÆ'), ('ÆStringÆ')
go

-- create test table CharacterMapping
create table CharacterMapping(SpecialCharacter char(1), MappedCharacter varchar(2))
insert CharacterMapping values('À', 'A'),('Æ', 'AE'), ('Ç', 'C')
go

--build the varchar for updating
declare @x varchar(max) = 'StringTest'
select @x = 'replace('+@x+', ''' + SpecialCharacter + ''','''+MappedCharacter+''')'  
from CharacterMapping
set @x = 'update vc set StringTest=' + @x +' from vc'

exec (@x)

select * from vc

结果：

StringAAE
AEStringAE

这篇关于SQL - 替换所有“ASCII /特殊字符”在字符串中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！