比如
SELECT replace( "a b c", ' ', '-' ) AS STRING
得到mysql> select replace('a b c',' ','-');
+----------------------------+
| replace('a b c',' ','-') |
+----------------------------+
| a---b-c |
+----------------------------+
1 row in set (0.00 sec)mysql>
问题是如何把多个空格替换成一个'-'字符,最终结果应该是: a-b-c
SELECT replace( "a b c", ' ', '-' ) AS STRING
得到mysql> select replace('a b c',' ','-');
+----------------------------+
| replace('a b c',' ','-') |
+----------------------------+
| a---b-c |
+----------------------------+
1 row in set (0.00 sec)mysql>
问题是如何把多个空格替换成一个'-'字符,最终结果应该是: a-b-c
+----------------------------------------------------------------+
| replace(replace(replace('a b c',' ',' '),' ',' '),' ','-') |
+----------------------------------------------------------------+
| a-b-c |
+----------------------------------------------------------------+
1 row in set (0.00 sec)mysql>
未来这个函数名可能叫做:SUBSTITUTE
bugid: 27389
BEGIN
DECLARE i,j INTEGER;
DECLARE ff1,ff2 VARCHAR(1000);
SET ff1='';
SET j=1;
SET i=LENGTH(ff);
WHILE j<=i DO
BEGIN
SET ff2=MID(ff,j,1);
IF ASCII(ff2)<>32 THEN
SET ff1=CONCAT(ff1,ff2,'-');
END IF;
SET j=j+1;
END;
END WHILE;
SELECT MID(ff1,1,LENGTH(ff1)-1);
END$$DELIMITER ;CALL dd2('a b c d');
mysql的正则处理支持比较弱,没有类似oracle的正则替换函数regexp_replace
希望未来能够有类似的东西吧。
LSB1:字段ID,内容1-100000SELECT GROUP_CONCAT(MID(a1,b.id,1) SEPARATOR '-' ) FROM (
SELECT ('a b c') AS a1) a LEFT JOIN zz.lsb1 b ON LENGTH(a1)>=b.id WHERE MID(a1,b.id,1)<>' '
SELECT ('a b c d') AS a1) a LEFT JOIN zz.lsb1 b ON LENGTH(a1)>=b.id WHERE MID(a1,b.id,1)<>' '