Split Varchar into a character in MySQL

Question

I have a varchar field of length 6. I want to split into separate characters in MySQL. I have currently tried using the 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.

Thanks.

Answer 1

There is no line splitting function in MySQL. so you need to create your own function. Use the link below. It will help you

Separated Separated Strings

The following example function takes 3 parameters, performs an operation using the 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, ''); 

Using

 SELECT SPLIT_STR(string, delimiter, position) 

Example

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

Credits: http://blog.fedecarg.com/2009/02/22/mysql-split-string-function/

Answer 2

Another solution would be to use any (unknown) scripting language that you use to separate. Otherwise, if it works, do not correct it.

To add some actual value to this answer, I add that it would be good practice for a meaningful column name:

 col = '123456'; { select SUBSTRING(col,1, 1) AS value1, SUBSTRING(col, 2,1) AS value2, SUBSTRING(col, 3,1) AS value3, SUBSTRING(col, 4,1) AS value4, SUBSTRING(col, 5,1) AS value5, SUBSTRING(col, 6,1) AS value6 from tbl_table } 

In addition, if you do this, it means that you are using the RDBMS incorrectly, and each of them should be in a separate field.