MySQL 从列中删除不需要的字符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4991284/
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
Removing unwanted character from column
提问by Nav Ali
I would like to remove the character '?' from column
我想删除字符“？” 从列
Column Name:
列名：
asds?dfgdfg
asds?dfgdfg
dfgwer?werwer
dfgwer?werwer
And Want to replace it with space
并想用空间代替它
Column Name:
列名：
asds dfgdfg
asds dfgdfg
dfgwer werwer
dfgwer werwer
回答by Hammerite
That is a Unicode replacement character. If this character is appearing in your table then it might be that you are issuing queries using the wrong character set. You should check the column character set, and you should also check the character set(s) of the connection(s) you use to issue queries. If there is a difference in connection character set between connections used to read and record data, or if there is a difference in expected character set between applications/scripts used to access the data, that would explain the presence of these characters.
那是一个Unicode 替换字符。如果此字符出现在您的表中，则可能是您使用错误的字符集发出查询。您应该检查列字符集，并且还应该检查用于发出查询的连接的字符集。如果用于读取和记录数据的连接之间的连接字符集存在差异，或者用于访问数据的应用程序/脚本之间的预期字符集存在差异，则可以解释这些字符的存在。
If you just want to replace it with a space:
如果您只想用空格替换它：
UPDATE myTable SET myColumn = REPLACE(myColumn, '?', ' ')
回答by Umar Adil
Replace below characters
替换以下字符
~ ! @ # $ % ^ & * ( ) _ +
` - =
{ } |
[ ] \
: "
; '
< > ?
, .
with this SQL
用这个 SQL
SELECT note as note_original,
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(note, '"', ''),
'.', ''),
'?', ''),
'`', ''),
'<', ''),
'=', ''),
'{', ''),
'}', ''),
'[', ''),
']', ''),
'|', ''),
'\'', ''),
':', ''),
';', ''),
'~', ''),
'!', ''),
'@', ''),
'#', ''),
'$', ''),
'%', ''),
'^', ''),
'&', ''),
'*', ''),
'_', ''),
'+', ''),
',', ''),
'/', ''),
'(', ''),
')', ''),
'-', ''),
'>', ''),
' ', '-'),
'--', '-') as note_changed FROM invheader
回答by Nikhil Srivastava
You Can Try this for multiple column
你可以试试这个多列
UPDATE myTable SET myColumn1 = REPLACE(myColumn1, '?', ' '),myColumn2 = REPLACE(myColumn2, '?', ' '),myColumn3 = REPLACE(myColumn3, '?', ' '),...;
更新 myTable SET myColumn1 = REPLACE(myColumn1, '?', ' '),myColumn2 = REPLACE(myColumn2, '?', ' '),myColumn3 = REPLACE(myColumn3, '?', ' '),...;
回答by Praveen D
Use this query to change the charset: SET CHARSET 'utf8';
使用此查询更改字符集：SET CHARSET 'utf8';
回答by Praveen D
Execute below query to set charset
执行以下查询以设置字符集
SET CHARSET 'utf8';
set names 'utf8'
