如何在 SQL 中替换字符串中的字符？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9621778/
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
How to replace a character from a String in SQL?
提问by WowBow
I have 100's of cells in our database which contain ?instead of '. It is possible that this might happen in all rows and columns and in more than one word per cell. Here is an example of just one cell.
我的数据库中有 100 个单元格，其中包含?而不是'. 这可能发生在所有行和列以及每个单元格中的多个单词中。这是一个只有一个单元格的示例。
Parents? CUI assumed equal to the sum of the father?s/stepfather?s and mother?s/ stepmother?s income .
I want to write an SQL statement which finds all the cells that contain ?(may be more than one per cell) and replace them with '. I am sure that all ?have to be replaced without exception.
我想编写一个 SQL 语句来查找包含的所有单元格?（每个单元格可能不止一个）并将它们替换为'. 我相信所有的?都必须无一例外地被替换。
I know there is a function replace but I couldn't know how to extract a character from a string in sql.
我知道有一个函数替换，但我不知道如何从 sql 中的字符串中提取一个字符。
This is one example I got but it couldn't help me.
这是我得到的一个例子，但它对我没有帮助。
UPDATE dbo.authors
SET city = replace(city, 'Salt', 'Olympic')
WHERE city LIKE 'Salt%';
Any ideas?
有任何想法吗？
回答by Justin Cave
Are you sure that the data stored in the database is actually a question mark? I would tend to suspect from the sample data that the problem is one of character set conversion where ?is being used as the replacement character when the character can't be represented in the client character set. Possibly, the database is actually storing Microsoft "smart quote" characters rather than simple apostrophes.
您确定数据库中存储的数据实际上是一个问号吗？我倾向于从示例数据中怀疑问题是字符集转换之一，?当字符不能在客户端字符集中表示时，它被用作替换字符。可能，该数据库实际上存储的是 Microsoft 的“智能引号”字符，而不是简单的撇号。
What does the DUMPfunction show is actually stored in the database?
什么是DUMP性能表现实际上是存储在数据库中？
SELECT column_name,
dump(column_name,1016)
FROM your_table
WHERE <<predicate that returns just the sample data you posted>>
What application are you using to view the data? What is the client's NLS_LANGset to?
您使用什么应用程序查看数据？客户的NLS_LANG设置是什么？
What is the database and national character set? Is the data stored in a VARCHAR2column? Or NVARCHAR2?
什么是数据库和国家字符集？数据是否存储在VARCHAR2列中？或者NVARCHAR2？
SELECT parameter, value
FROM v$nls_parameters
WHERE parameter LIKE '%CHARACTERSET';
If all the problem characters are stored in the database as 0x19 (decimal 25), your REPLACEwould need to be something like
如果所有问题字符都以 0x19（十进制 25）的形式存储在数据库中，则您REPLACE需要类似于
UPDATE table_name
SET column1 = REPLACE(column1, chr(25), q'[']'),
column2 = REPLACE(column2, chr(25), q'[']'),
...
columnN = REPLACE(columnN, chr(25), q'[']')
WHERE INSTR(column1,chr(25)) > 0
OR INSTR(column2,chr(25)) > 0
...
OR INSTR(columnN,chr(25)) > 0
回答by JesseBuesking
UPDATE databaseName.tableName
SET columnName = replace(columnName, '?', '''')
WHERE columnName LIKE '%?%'
回答by Curt
This will replace all ?with ':
这将全部替换?为'：
UPDATE dbo.authors
SET city = replace(city, '?', '''')
WHERE city LIKE '%?%'
If you need to update more than one column, you can either change cityeach time you execute to a different column name, or list the columns like so:
如果您需要更新多个列，您可以在city每次执行时更改为不同的列名，或者像这样列出列：
UPDATE dbo.authors
SET city = replace(city, '?', '''')
,columnA = replace(columnA, '?', '''')
WHERE city LIKE '%?%'
OR columnA LIKE '%?%'
