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)