mysql把一个字段分成多个字段_sql 把一个字段分成多个字段

abc1aaa-bbb-bbb232vvv-nnnn243qqq-www-wwww12变成如下形式：ab1b2b3c1aaabbbbbb232vvvnnnn243qqqwwwwwww12我要mssql版本的别给我Mysql和Oracle的mysql>select*fromt_fengchujun;+---...

a b c

1 aaa-bbb-bbb 23

2 vvv-nnnn 24

3 qqq-www-wwww 12

变成如下形式：

a b1 b2 b3 c

1 aaa bbb bbb 23

2 vvv nnnn 24

3 qqq www wwww 12

我要mssql版本的 别给我Mysql和Oracle的

mysql> select * from t_fengchujun;

+------+--------------------+------+

| 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 |

就是这种效果如何达到，上边是mysql的 希望能转换成mssql的

展开