如何在SQL中替换字符串中的字符? [英] How to replace a character from a String in SQL?

问题描述

我的数据库中有100个单元格，其中包含?而不是'.这有可能发生在所有行和列中，并且每个单元格中的单词超过一个.这是一个单元格的示例.

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.

Parents? CUI assumed equal to the sum of the father?s/stepfather?s and mother?s/ stepmother?s income .

我想编写一条SQL语句，查找包含?的所有单元格(每个单元格可以包含多个单元格)，然后将其替换为'.我确信所有?都必须无一例外地更换.

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.

这是我得到的一个例子，但无济于事.

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%';

有什么想法吗?

推荐答案

您确定数据库中存储的数据实际上是一个问号吗?我倾向于从样本数据中怀疑问题出在字符集转换之一，其中当不能在客户端字符集中表示该字符时，将?用作替换字符.可能该数据库实际上存储的是Microsoft智能引号"字符，而不是简单的撇号.

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.

DUMP函数显示的内容实际上存储在数据库中吗?

What does the DUMP function show is actually stored in the database?

SELECT column_name,
       dump(column_name,1016)
  FROM your_table
 WHERE <<predicate that returns just the sample data you posted>>

您使用哪个应用程序查看数据?客户端的NLS_LANG设置为什么?

What application are you using to view the data? What is the client's NLS_LANG set to?

什么是数据库和国家字符集?数据是否存储在VARCHAR2列中?还是NVARCHAR2?

What is the database and national character set? Is the data stored in a VARCHAR2 column? Or NVARCHAR2?

SELECT parameter, value
  FROM v$nls_parameters
 WHERE parameter LIKE '%CHARACTERSET';

如果所有问题字符都以0x19(十进制25)的形式存储在数据库中，则您的REPLACE必须是类似

If all the problem characters are stored in the database as 0x19 (decimal 25), your REPLACE would need to be something like

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

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