mysql中利用sql语句来提取分割符

下面一起来看一个mysql中利用sql语句来提取分割符,希望例子对各位会带来帮助.

1 K_GLF|K_GLJYJE|K_GLJYSBM|K_GLNR|K_BM|K_DXBM|K_XJLLB

2 K_GLF|K_GLJYJE|K_DXBM|K_BM|K_XJLLB

3 K_GLF|K_BM|K_XJLLB

如何用SQL取出结果,把里面的 “|”给提取出来,代码如下:

1. 1   |||||| 
2. 2   |||| 
3. 3   || 
4.  
5. mysql> select *  from test; 
6. +—————————————————–+ 
7. | name                                                | 
8. +—————————————————–+ 
9. | K_GLF|K_GLJYJE|K_GLJYSBM|K_GLNR|K_BM|K_DXBM|K_XJLLB | 
10. | K_GLF|K_GLJYJE|K_DXBM|K_BM|K_XJLLB                  | 
11. | K_GLF|K_BM|K_XJLLB                                  | 
12. +—————————————————–+ 
13. 3 rows in set (0.00 sec) 
14. mysql>  select repeat(‘|’,length(name)-length(replace(name,’|',”))) from test; 
15. +——————————————————-+ 
16. | repeat(‘|’,length(name)-length(replace(name,’|',”))) | 
17. +——————————————————-+ 
18. | ||||||                                                | 
19. | ||||                                                  | 
20. | ||                                                    | 
21. +——————————————————-+ 
22. 3 rows in set (0.00 sec)//开源代码phpfensi.com