MySQL：如何从字符串中删除双倍或更多空格？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6940646/
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
MySQL : how to remove double or more spaces from a string?
提问by Dylan
I couldn't find this question for MySQL so here it is:
我找不到 MySQL 的这个问题，所以这里是：
I need to trim all double or more spaces in a string to 1 single space.
我需要将字符串中的所有双倍或更多空格修剪为 1 个单空格。
For example: "The Quick Brown Fox" should be : "The Quick Brown Fox"
例如：“The Quick Brown Fox”应该是：“The Quick Brown Fox”
The function REPLACE(str, " ", " ") only removes double spaces, but leaves multiples spaces when there are more...
函数 REPLACE(str, " ", " ") 只删除双空格，但当有更多空格时会留下多个空格...
回答by alkoln
The shortest and, surprisingly, the fastest solution:
最短且令人惊讶的是最快的解决方案：
CREATE FUNCTION clean_spaces(str VARCHAR(255)) RETURNS VARCHAR(255)
BEGIN
while instr(str, ' ') > 0 do
set str := replace(str, ' ', ' ');
end while;
return trim(str);
END
回答by Артур Курицын
DELIMITER //
DROP FUNCTION IF EXISTS DELETE_DOUBLE_SPACES//
CREATE FUNCTION DELETE_DOUBLE_SPACES ( title VARCHAR(250) )
RETURNS VARCHAR(250) DETERMINISTIC
BEGIN
DECLARE result VARCHAR(250);
SET result = REPLACE( title, ' ', ' ' );
WHILE (result <> title) DO
SET title = result;
SET result = REPLACE( title, ' ', ' ' );
END WHILE;
RETURN result;
END//
DELIMITER ;
SELECT DELETE_DOUBLE_SPACES('a b');
回答by tomconnors
I know this question is tagged with mysql, but if you're fortunate enough to use MariaDB you can do this more easily:
我知道这个问题是用 mysql 标记的，但如果你有幸使用 MariaDB，你可以更轻松地做到这一点：
SELECT REGEXP_REPLACE(column, '[[:space:]]+', ' ');
回答by nobody
This solution isn't very elegant but since you don't have any other option:
这个解决方案不是很优雅，但因为你没有任何其他选择：
UPDATE t1 set str = REPLACE( REPLACE( REPLACE( str, " ", " " ), " ", " " ), " ", " " );
回答by xytyx
Here's an old trick that does not require regular expressions or complicated functions.
这是一个不需要正则表达式或复杂函数的老技巧。
You can use the replace function 3 times like so:
您可以像这样使用替换功能 3 次：
REPLACE('This is my long string',' ','<>')
becomes:
变成：
This<>is<><><><>my<><><>long<><><><>string
Then you replace all occurrences of '><' with an empty string '' by wrapping it in another replace:
然后将所有出现的 '><' 替换为空字符串 ''，将其包装在另一个替换中：
REPLACE(
REPLACE('This is my long string',' ','<>'),
'><',''
)
This<>is<>my<>long<>string
Then finally one last replace converts the '<>' back to a single space
然后最后一次替换将 '<>' 转换回单个空格
REPLACE(
REPLACE(
REPLACE('This is my long string',
' ','<>'),
'><',''),
'<>',' ')
This is my long string
This example was created in MYSQL (put a SELECT in front) but works in many languages.
此示例是在 MYSQL 中创建的（在前面放置一个 SELECT），但适用于多种语言。
回答by sakhunzai
After searching I end up writing a function i.e
搜索后，我最终编写了一个函数，即
drop function if exists trim_spaces;
如果存在trim_spaces，则删除函数；
delimiter $$
CREATE DEFINER=`root`@`localhost` FUNCTION `trim_spaces`(`dirty_string` text, `trimChar` varchar(1))
RETURNS text
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
declare cnt,len int(11) ;
declare clean_string text;
declare chr,lst varchar(1);
set len=length(dirty_string);
set cnt=1;
set clean_string='';
while cnt <= len do
set chr=right(left(dirty_string,cnt),1);
if chr <> trimChar OR (chr=trimChar AND lst <> trimChar ) then
set clean_string =concat(clean_string,chr);
set lst=chr;
end if;
set cnt=cnt+1;
end while;
return clean_string;
END
$$
delimiter ;
USAGE:
用法：
set @str='------apple--------banana-------------orange---' ;
set @str='------apple--------banana-------------orange---' ;
select trim_spaces( @str,'-')
select trim_spaces( @str,'-')
output: apple-banana-orange-
输出： apple-banana-orange-
parameter trimCharto function could by any character that is repeating and you want to remove .
trimChar函数的参数可以是任何重复的字符并且您想要删除 .
Note it will keep first character in repeating set
请注意，它将保留重复集中的第一个字符
cheers :)
欢呼:)
回答by sakhunzai
If you are using php....
如果您使用的是 php....
try{
$con = new PDO ("mysql:host=localhost;dbname=dbasename","root","");
}
catch(PDOException $e){
echo "error".$e-getMessage();
}
$select = $con->prepare("SELECT * FROM table");
$select->setFetchMode(PDO::FETCH_ASSOC);
$select->execute();
while($data=$select->fetch()){
$id = $data['id'];
$column = $data['column'];
$column = trim(preg_replace('/\s+/',' ', $column)); // remove all extra space
$update = $con->prepare("UPDATE table SET column=:column WHERE id='$id'");
$update->bindParam(':column', $column );
$update->execute();
// echo $column."<br>";
}
回答by Andre
Follow my generic function made for MySQL 5.6. My intention was to use regular expression to identify the spaces, CR and LF, however, it is not supported by this version of mysql. So, I had to loop through the string looking for the characters.
按照我为 MySQL 5.6 制作的通用函数。我的意图是使用正则表达式来识别空格、CR 和 LF，但是，此版本的 mysql 不支持它。所以，我不得不遍历字符串寻找字符。
CREATE DEFINER=`db_xpto`@`%` FUNCTION `trim_spaces_and_crlf_entire_string`(`StringSuja` text) RETURNS text CHARSET utf8 COLLATE utf8_unicode_ci
DETERMINISTIC
BEGIN
DECLARE StringLimpa TEXT;
DECLARE CaracterAtual, CaracterAnterior TEXT;
DECLARE Contador, TamanhoStringSuja INT;
SET StringLimpa = '';
SET CaracterAtual = '';
SET CaracterAnterior = '';
SET TamanhoStringSuja = LENGTH(StringSuja);
SET Contador = 1;
WHILE Contador <= TamanhoStringSuja DO
SET CaracterAtual = SUBSTRING(StringSuja, Contador, 1);
IF ( CaracterAtual = ' ' AND CaracterAnterior = ' ' ) OR CaracterAtual = '\n' OR CaracterAtual = '\r' THEN
/* DO NOTHING */
SET Contador = Contador;
/* TORNA OS ESPA?OS DUPLICADOS, CR, LF VISUALIZáVEIS NO RESULTADO (DEBUG)
IF ( CaracterAtual = ' ' ) THEN SET StringLimpa = CONCAT(StringLimpa, '*');END IF;
IF ( CaracterAtual = '\n' ) THEN SET StringLimpa = CONCAT(StringLimpa, '\N');END IF;
IF ( CaracterAtual = '\r' ) THEN SET StringLimpa = CONCAT(StringLimpa, '\R');END IF;
*/
ELSE
/* COPIA CARACTER ATUAL PARA A STRING A FIM DE RECONSTRUí-LA SEM OS ESPA?OS DUPLICADOS */
SET StringLimpa = CONCAT(StringLimpa, CaracterAtual);
/*SET StringLimpa = CONCAT(StringLimpa, Contador, CaracterAtual);*/
SET CaracterAnterior = CaracterAtual;
END IF;
SET Contador = Contador + 1;
END WHILE;
RETURN StringLimpa;
END
回答by Imranul Islam
If you want to update the existing column which has multiple spaces into one then this update query will be helpful:
如果要将具有多个空格的现有列更新为一个，则此更新查询将很有帮助：
UPDATE
your_tableSETcolumn_that_you_want_to_change= REGEXP_REPLACE(column_that_you_want_to_change, '[[:space:]]+', ' ');
UPDATE
your_tableSETcolumn_that_you_want_to_change= REGEXP_REPLACE(column_that_you_want_to_change, '[[:space:]]+', ' ');
回答by A. K.
This is slightly general solution: from
这是稍微通用的解决方案：从
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56195&whichpage=1
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56195&whichpage=1
create table t (s sysname)
insert into t select 'The Quick Brown Fox'
-- convert tabs to spaces
update t set s = replace(s, ' ',' ')
where charindex(' ', s) > 0
-- now do the work.
while 1=1
begin
update t
set s = substring(s, 1, charindex(' ', s, 1)-1) + ' ' + ltrim(substring(s,charindex(' ', s, 1), 8000))
where charindex(' ', s, 1) > 0
if @@rowcount = 0
break
end
select s
from t
