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||
5. mysql>select*fromtest;
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. 3rowsinset(0.00sec)
14. mysql>selectrepeat(‘|’,length(name)-length(replace(name,’|',”)))fromtest;
15. +——————————————————-+
16. |repeat(‘|’,length(name)-length(replace(name,’|',”)))|
17. +——————————————————-+
18. ||||||||
19. ||||||
20. ||||
21. +——————————————————-+
22. 3rowsinset(0.00sec)//开源代码phpfensi.com