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   ||||||
2   ||||
3   ||
 
mysql> select *  from test;
+—————————————————–+
| name                                                |
+—————————————————–+
| K_GLF|K_GLJYJE|K_GLJYSBM|K_GLNR|K_BM|K_DXBM|K_XJLLB |
| K_GLF|K_GLJYJE|K_DXBM|K_BM|K_XJLLB                  |
| K_GLF|K_BM|K_XJLLB                                  |
+—————————————————–+
3 rows in set (0.00 sec)
mysql>  select repeat(‘|’,length(name)-length(replace(name,’|',”))) from test;
+——————————————————-+
| repeat(‘|’,length(name)-length(replace(name,’|',”))) |
+——————————————————-+
| ||||||                                                |
| ||||                                                  |
| ||                                                    |
+——————————————————-+
3 rows in set (0.00 sec)