如a
id title
1 aa,bb,,cc,
2 ss,dd,gg
3 ee,ssss,ggg,,
4 eee,,,www,,,eee,,
5 eeww,ee,rrr,
不知道sql能不能替换掉如下结果
a
id title
1 aa,bb,cc
2 ss,dd,gg
3 ee,ssss,ggg
4 eee,www,eee
5 eeww,ee,rrrtitle后面有逗号,不管多少个去掉
title中间有两个或多个只保留一个所写的sql可以多条也可以运行几次替换能达到最终效果就行在这里先说谢谢了
id title
1 aa,bb,,cc,
2 ss,dd,gg
3 ee,ssss,ggg,,
4 eee,,,www,,,eee,,
5 eeww,ee,rrr,
不知道sql能不能替换掉如下结果
a
id title
1 aa,bb,cc
2 ss,dd,gg
3 ee,ssss,ggg
4 eee,www,eee
5 eeww,ee,rrrtitle后面有逗号,不管多少个去掉
title中间有两个或多个只保留一个所写的sql可以多条也可以运行几次替换能达到最终效果就行在这里先说谢谢了
update a set title=replace(title,',,',',')
反复运行,直到影响行数为0
替换成
1 aa,bb,cc
怎么替换
mysql> select mid('123,',1,length('123,')-1);
+--------------------------------+
| mid('123,',1,length('123,')-1) |
+--------------------------------+
| 123 |
+--------------------------------+
1 row in set (0.00 sec)
SELECT a.*,MID(CONCAT(',',a.title,','),b.id,1),b.id AS newid,
CONCAT(',',a.title,','),
REPLACE(
MID(CONCAT(',',a.title,','),b.id,
INSTR(MID(CONCAT(a.title,','),b.id,LENGTH(CONCAT(a.title,','))),',')+1),',','') AS aa1,
LENGTH(REPLACE(
MID(CONCAT(',',a.title,','),b.id,
INSTR(MID(CONCAT(a.title,','),b.id,LENGTH(CONCAT(a.title,','))),',')+1),',','')) AS aa2
FROM ttyh a LEFT JOIN lsb1 b
ON LENGTH(a.title)+2>=b.id
WHERE MID(CONCAT(',',a.title,','),b.id,1)=','
AND
LENGTH(REPLACE(
MID(CONCAT(',',a.title,','),b.id,
INSTR(MID(CONCAT(a.title,','),b.id,LENGTH(CONCAT(a.title,','))),',')+1),',',''))>=2
ORDER BY a.id,newid) dd GROUP BY idlsb1:字段ID,内容1-10000
是什么意思是不是要加一个ttyh 字段lsb1
把ss,dd,gg也改成ss,dd,g了
我只要有逗号在后面才替换
lsb1:字段ID,内容1-10000
concat('aa,bb,cc',',')
+----+----------------------+
| id | GROUP_CONCAT(dd.aa1) |
+----+----------------------+
| 1 | aa |
| 2 | aa,dd |
| 3 | aa,dddf |
| 4 | aa,dddf |
| 5 | aaff,ddd |
| 6 | aaff,dddf |
| 7 | aaff,dddf |
| 8 | aaff,dddf |
+----+----------------------+还是
update a set title=mid(title,1,length(title)-1)
update a set title=mid(title,1,length(title)-1) where right(title,1)=',';
好使
多谢各位的帮忙
mysql> CALL KILLCOMMA
-> ;
+----+----------------------+
| id | group_concat(dd.aa1) |
+----+----------------------+
| 1 | aa,bb,cc |
| 2 | ss,dd,gg |
| 3 | ssss,ggg,ee |
| 4 | eee,www,eee |
| 5 | rrr,eeww,ee |
+----+----------------------+
5 rows in set (0.01 sec)Query OK, 0 rows affected (0.03 sec)KILLCOMMA代码为5楼的