mysql varchar 字符,在MySQL中将Varchar拆分为字符

I have a varchar field with length 6. I want to split into single characters in MySQL. Currently I have tried with following script:

col = '123456';

{

select SUBSTRING(col,1, 1),

SUBSTRING(col, 2,1),

SUBSTRING(col, 3,1),

SUBSTRING(col, 4,1),

SUBSTRING(col, 5,1),

SUBSTRING(col, 6,1)

from tbl_table

}

The above script works but is there any other solution for this.

Thank You.

解决方案

There is no string split function in MySQL. so you have to create your own function. Use below link.This will help you

Split delimited strings

The following example function takes 3 parameters, performs an operation using an SQL function, and returns the result.

Function

CREATE FUNCTION SPLIT_STR(

x VARCHAR(255),

delim VARCHAR(12),

pos INT

)

RETURNS VARCHAR(255)

RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),

LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),

delim, '');

Usage

SELECT SPLIT_STR(string, delimiter, position)

Example

SELECT SPLIT_STR('a|bb|ccc|dd', '|', 3) as third;

+-------+

| third |

+-------+

| ccc |

+-------+