split string by chunk length in mysql

Question

I have a string like this: 0002000B0016001M001S002A (variable string length but divisible by 4)

I need to split it (in MySql) in chunks of 4 characters

Espected result :

@my_var = '0002'

@my_var = '000B'

@my_var = '0016'

@my_var = '001M'

@my_var = '001S'

@my_var = '002A'

I think SUBSTR() can be employed here but I don't know how to manage next position in a loop in mysql

Or some equivalent like chunk_split() in PHP for MySql?

Possible duplicate: https://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows — Tim Biegeleisen, Jan 22 '18 at 01:55
@ Tim Biegeleisen Your mentioned answer use one separator ',' here the question is to split by length not by separator — MTK, Jan 22 '18 at 01:58
@ Racil Hilan I have thought of this as the last remedy but this requires to make two query — MTK, Jan 22 '18 at 02:19
From a row containing an N length string, you want to produce N/4 rows as result? — bishop, Jan 22 '18 at 02:22
Yes for N/4 ... but not to produce rows ... I need to asign each chunk to @user_variables inside one function or stored procedure. `@var1 = '0002'` `@var1 = '000B'` ... and so on — MTK, Jan 22 '18 at 02:24
*but this requires to make two query*, why two queries? Is there something else that you want to retrieve based on part of the string? — Racil Hilan, Jan 22 '18 at 02:28
two query because the original string is inside DB and the results I need to use also inside in one function or stored proceure — MTK, Jan 22 '18 at 02:30
Then inside that function or stored procedure you can write a loop that splits the data first. Similar to PHP, it is easy to split the data in loop inside a function or stored procedure, but you cannot write loops inside queries. — Racil Hilan, Jan 22 '18 at 03:00

score 2 · Accepted Answer · answered Jan 22 '18 at 02:38

So, you want to use each 4-byte long sequence as part of a procedure, but not quite sure how to use SUBSTR to do it? Then, something like:

DELIMITER #
CREATE PROCEDURE split_string_in_fours(string VARCHAR(256))
BEGIN
  DECLARE len INT UNSIGNED DEFAULT 0;
  DECLARE pos INT UNSIGNED DEFAULT 1;
  DECLARE var CHAR(4) DEFAULT '';

  SET len = LENGTH(string);

  WHILE pos < len DO
    SET var=SUBSTR(string, pos, 4);
    SET pos=pos+4;
  END WHILE;
END #
DELIMITER ;

Caveat emptor: untested. Here, var contains the 4-byte sequence you want. The procedure takes the string on which to split. You may already have this in a procedure as the result of a SELECT, in which case you can use it directly

split string by chunk length in mysql

1 Answers1