求sql语句将一个字段。按指定字符分成几个字段 比如,有表1 如下a b 字段 c
1 fsdf-fefw-few-fegh d
2 fhtre-reh-eh-jeg fd
3 hjre-heg-wfbc d
将b字段 以 - 分成几个字段。
1 fsdf-fefw-few-fegh d
2 fhtre-reh-eh-jeg fd
3 hjre-heg-wfbc d
将b字段 以 - 分成几个字段。
调试欢乐多
SUBSTRING_INDEX(b,'-',1) as b1,
SUBSTRING_INDEX(SUBSTRING_INDEX(b,'-',2),'-',-1) as b2,
SUBSTRING_INDEX(SUBSTRING_INDEX(b,'-',3),'-',-1) as b3,
SUBSTRING_INDEX(SUBSTRING_INDEX(b,'-',8),'-',-1) as b4,
c
from 表1;
+------+--------------------+------+
| a | b | c |
+------+--------------------+------+
| 1 | fsdf-fefw-few-fegh | d |
| 2 | fhtre-reh-eh-jeg | fd |
| 3 | hjre-heg-wfbc | d |
+------+--------------------+------+
3 rows in set (0.00 sec)mysql> select a,
-> SUBSTRING_INDEX(concat(b,'----'),'-',1) as b1,
-> SUBSTRING_INDEX(SUBSTRING_INDEX(concat(b,'----'),'-',2),'-',-1) as b2,
-> SUBSTRING_INDEX(SUBSTRING_INDEX(concat(b,'----'),'-',3),'-',-1) as b3,
-> SUBSTRING_INDEX(SUBSTRING_INDEX(concat(b,'----'),'-',4),'-',-1) as b4,
-> c
-> from t_fengchujun;
+------+-------+------+------+------+------+
| a | b1 | b2 | b3 | b4 | c |
+------+-------+------+------+------+------+
| 1 | fsdf | fefw | few | fegh | d |
| 2 | fhtre | reh | eh | jeg | fd |
| 3 | hjre | heg | wfbc | | d |
+------+-------+------+------+------+------+
3 rows in set (0.00 sec)
SUBSTRING_INDEX(concat(b,'-'),'-',1) as b1,
SUBSTRING_INDEX(SUBSTRING_INDEX(concat(b,'--'),'-',2),'-',-1) as b2,
SUBSTRING_INDEX(SUBSTRING_INDEX(concat(b,'---'),'-',3),'-',-1) as b3,
SUBSTRING_INDEX(SUBSTRING_INDEX(concat(b,'----'),'-',4),'-',-1) as b4,
c
from t_fengchujun;
因为要处理的数据,可能有50万条。