SQL - 替换字符串中的所有“ASCII/特殊字符”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22238467/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL - Replacing all "ASCII/special characters" in a string
提问by BrettKB
Edit: 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.
编辑：我有大约 80 个字符导致我的应用程序出现问题，所以我不想为每个字符硬编码一个 REPLACE。我认为创建一个包含两列“特殊字符”和“替换字符”的单独表会更容易，我将从包含“StringTest”列的原始表中删除这些列。我的目标是弄清楚如何使用字符表来替换字符串表中的字符。
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:
我试图在 SQL Server 中用“MappedCharacters”（A、AE、C）替换所有“特殊字符”（即 à、?、?）。我尝试了两种不同的技术，一种使用游标，一种不使用游标，来搜索字符串并用映射字符替换所有特殊字符。我的每个方法只替换它们与字符串在同一行中的字符。之前的例子：
num SpecialCharacter MappedCharacter StringTest
1 à A Stringà?
2 ? AE ?String?
3 ? C Str?àing
Example after:
之后的示例：
num SpecialCharacter MappedCharacter StringTest
1 à A StringA?
2 ? AE AEStringAE
3 ? C StrCàing
Preferred Output:
首选输出：
num SpecialCharacter MappedCharacter StringTest
1 à A StringAAE
2 ? AE AEStringAE
3 ? C StrCAing
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.
所以你可以看到我想替换 StringTest 中的所有“特殊字符”，但只有在同一行中的字符才会被替换。
I haven't quite figured out how to do that just yet.
我还没有完全弄清楚如何做到这一点。
Here are the two SQL code that I have been trying to modify (I only need one to work)
这是我一直在尝试修改的两个 SQL 代码（我只需要一个就可以工作）
First Method:
第一种方法：
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;
Second Method:
第二种方法：
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
回答by t-clausen.dk
Try this, it works better than looping because there is only 1 update:
试试这个，它比循环更有效，因为只有 1 个更新：
-- 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
Result:
结果：
StringAAE
AEStringAE
回答by clhereistian
I would make a separate mapping table which contains the bad character and its corresponding good character, one set per row. Then loop over that table and do a replace for each character set.
我会制作一个单独的映射表，其中包含坏字符及其相应的好字符，每行一组。然后遍历该表并对每个字符集进行替换。
DECLARE @map TABLE (
id INT,
badChar CHAR,
goodChar CHAR
)
DECLARE @strings TABLE (
searchString VARCHAR(50)
)
INSERT INTO @map
VALUES
(1, 'y', 'a'),
(2, 'z', 'b')
DECLARE @curRow INT, @totalRows INT
SET @curRow = 1
SELECT @totalRows = COUNT(*) FROM @map
INSERT INTO @strings
VALUES
('zcccyccz'),
('cccyccz')
WHILE @curRow <= @totalRows
BEGIN
UPDATE @strings
SET searchString = REPLACE(searchString, badChar, goodChar)
FROM @map
WHERE id = @curRow
SET @curRow = @curRow + 1
END
SELECT * FROM @strings
--Output
--bcccaccb
--cccaccb
回答by paparazzo
update table
set column = REPLACE(column,'à','A')
where column like ('%à%')
update table
set column = REPLACE(column,'?','AE')
where column like ('%?%')
I will leave the 3rd to you
我会把第三个留给你
Or this might be more efficient
或者这可能更有效
update table
set column = REPLACE(REPLACE(column,'à','A'),'?','AE')
where column like ('%à%')
or column like ('%?%')
If you really want to process a list of mapped characters then this is not a proper answer
如果您真的想处理映射字符列表，那么这不是一个正确的答案
回答by KM.
It would be helpful to know how many rows are in your table and how many you estimate to have "special characters". Also, are there only 3 special characters? if you have 40 or less special characters, it may look ridiculous, but I'd just nest as many REPLACE() calls as you have special characters, like:
了解表中有多少行以及您估计有多少行具有“特殊字符”会很有帮助。另外，只有3个特殊字符吗？如果您有 40 个或更少的特殊字符，它可能看起来很可笑，但我只会嵌套与您有特殊字符一样多的 REPLACE() 调用，例如：
UPDATE YourTable SET YourColumn = REPLACE(
REPLACE(
REPLACE(YourColumn,'?','C')
,'?','AE')
,'à','A')
if most rows have special characters, I'd skip any WHERE. if only a few rows have special characters, I'd use a CTE to identify them:
如果大多数行都有特殊字符，我会跳过任何WHERE. 如果只有几行有特殊字符，我会使用 CTE 来识别它们：
;WITH AllSpecialRows AS
(
SELECT PrimaryKey FROM YourTable WHERE YourColumn LIKE '%à%'
UNION
SELECT PrimaryKey FROM YourTable WHERE YourColumn LIKE '%?%'
UNION
SELECT PrimaryKey FROM YourTable WHERE YourColumn LIKE '%?%'
)
UPDATE y
SET YourColumn = REPLACE(
REPLACE(
REPLACE(YourColumn,'?','C')
,'?','AE')
,'à','A')
FROM YourTable y
INNER JOIN AllSpecialRows s ON y.PrimaryKey =s.PrimaryKey
回答by Sathish
@t-clausen.dk answer with Table variables and temp tables, just to avoid people mess up their dev databases with additional tables.
@t-clausen.dk 用表变量和临时表回答，只是为了避免人们用额外的表弄乱他们的开发数据库。
TABLE Variables:
表变量：
-- Create test table variable @CharacterMapping
DECLARE @CharacterMapping TABLE (SpecialCharacter char(1), MappedCharacter varchar(2))
INSERT @CharacterMapping VALUES('à', 'A'), ('?', 'AE'), ('?', 'C')
--Build the varchar for updating
DECLARE @x varchar(max) = 'StringTest'
SELECT @x = 'replace('+@x+', ''' + SpecialCharacter + ''',''' + MappedCharacter + ''')'
FROM @CharacterMapping
SET @x = 'DECLARE @vc TABLE(StringTest varchar(20));'
+ ' insert @vc values(''Stringà?''), (''?String?'');'
+ 'update @vc set StringTest=' + @x +' from @vc;'
+ 'SELECT * FROM @vc;'
Exec (@x)
GO
With Temp table:
带临时表：
-- Create test temp table #vc
CREATE TABLE #vc(StringTest varchar(20))
INSERT #vc VALUES('Stringà?'), ('?String?')
-- Create test table CharacterMapping
DECLARE @CharacterMapping TABLE (SpecialCharacter char(1), MappedCharacter varchar(2))
INSERT @CharacterMapping VALUES('à', 'A'), ('?', 'AE'), ('?', 'C')
--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'
-- Execute
EXEC (@x)
-- Select the results
SELECT * FROM #vc;
-- Drop temp table
DROP TABLE #vc;
GO
