求sql语句将一个字段。按指定字符分成几个字段

[daodaoyu222]

求sql语句将一个字段。按指定字符分成几个字段

比如，有表1 如下



a b 字段 c
1 fsdf-fefw-few-fegh d
2 fhtre-reh-eh-jeg fd
3 hjre-heg-wfbc d


将b字段 以 - 分成几个字段。

[ACMAIN_CHM]

select a,
	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;

[ACMAIN_CHM]

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   |
+------+-------+------+------+------+------+
3 rows in set (0.00 sec)

[平凡的思想者]

2楼的写法是正确的，不过填充的'-'字符数目只要跟位置匹配即可。

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;

[daodaoyu222]

还想问一下，这个 SUBSTRING_INDEX 处理的速度怎么样


因为要处理的数据，可能有50万条。

[ACMAIN_CHM]

[Quote]还想问一下，这个 SUBSTRING_INDEX 处理的速度怎么样[/Quote]
尚可，毕竟比其它的方法快一些。

最快的应该还是在你自己的程序中进行处理。

[nianzhang747]

类似此操作应该在程序中处理

数据库之存储字符床即可