How to add commas after every letter of random list of letters in database

Question

How can i add comma, after every letter if my all records have random letters?

What i have:

1. AHGJTOSIYGJ
2. OTPDBSKGY
3. HFRYEC
4. OPFKWIFS
// etc

What i need:

1. A, H, G, J, T, O, S, I, Y, G, J,
2. O, T, P, D, B, S, K, G, Y,
3. H, F, R, Y, E, C,
4. O, P, F, K, W, I, F, S,
// etc

Possible duplicate of [How to do a regular expression replace in MySQL?](http://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql) — Jens, Aug 25 '16 at 09:32

score 3 · Accepted Answer · answered Aug 25 '16 at 10:01

These operations should be done via the application level not by DB. However you really want to do this from DB level you can easily do so using the user defined function. Here is a function to do this

delimiter //

create function myFunction(myString varchar(255)) 
returns varchar(255)
begin
 declare strLen int ;
 declare lookupChar char(1);
 declare finalString varchar(255);
 declare x int;

 set strLen = length(myString);
 set x = 1 ;
 set finalString = '';
 while x <= strLen do
   set lookupChar = substring(myString,x,1);
   if finalString = '' then 
     set finalString = lookupChar;
   else 
     set finalString = concat(finalString,',',lookupChar);
   end if;
   set x = x+1;
 end while;
 return finalString;
end//

delimiter;

Lets run this on mysql

mysql> create table mytable (id int, value varchar(100));
Query OK, 0 rows affected (0.19 sec)

mysql> insert into mytable values (1,'AHGJTOSIYGJ'),(2,'OTPDBSKGY'),(3,'HFRYEC'),(4,'OPFKWIFS');
Query OK, 4 rows affected (0.02 sec)

mysql> select * from mytable ;
+------+-------------+
| id   | value       |
+------+-------------+
|    1 | AHGJTOSIYGJ |
|    2 | OTPDBSKGY   |
|    3 | HFRYEC      |
|    4 | OPFKWIFS    |
+------+-------------+
4 rows in set (0.00 sec)

Lets now create the function

mysql> delimiter //
mysql> create function myFunction(myString varchar(255)) 
    -> returns varchar(255)
    -> begin
    ->  declare strLen int ;
    ->  declare lookupChar char(1);
    ->  declare finalString varchar(255);
    ->  declare x int;
    -> 
    ->  set strLen = length(myString);
    ->  set x = 1 ;
    ->  set finalString = '';
    ->  while x <= strLen do
    ->    set lookupChar = substring(myString,x,1);
    ->    if finalString = '' then 
    ->      set finalString = lookupChar;
    ->    else 
    ->      set finalString = concat(finalString,',',lookupChar);
    ->    end if;
    ->    set x = x+1;
    ->  end while;
    ->  return finalString;
    -> end//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

So far so good, now lets select the values using the function

mysql> select id,myFunction(value) as value from mytable ;
+------+-----------------------+
| id   | value                 |
+------+-----------------------+
|    1 | A,H,G,J,T,O,S,I,Y,G,J |
|    2 | O,T,P,D,B,S,K,G,Y     |
|    3 | H,F,R,Y,E,C           |
|    4 | O,P,F,K,W,I,F,S       |
+------+-----------------------+

You can do this for entire table and also you can do the update easily if needed.

Mike · Answer 2 · 2016-08-25T10:08:32.110

We need work table of sequence numbers from 1 to maximal length input strings:

create table seqnum(X int not null, primary key(X));
insert into seqnum values(1),(2),(3),(4);
insert into seqnum select X+4 from seqnum;
insert into seqnum select X+8 from seqnum;
insert into seqnum select X+16 from seqnum;
insert into seqnum select X+32 from seqnum;

Test table:

create table test6(id int, lett varchar(100));
insert into test6 values(1,'AHGJTOSIYGJ'),(2,'OTPDBSKGY'),(3,'HFRYEC'),(4,'OPFKWIFS');

Query to select changed letters on table test6:

select id, lett, group_concat(substr(lett,S.X,1) ORDER BY T.id, S.x SEPARATOR ', ')
  from test6 T, seqnum S
 where S.X<=length(T.lett)
 group by id

Result:

1 | AHGJTOSIYGJ | A, H, G, J, T, O, S, I, Y, G, J
2 | OTPDBSKGY   | O, T, P, D, B, S, K, G, Y
3 | HFRYEC      | H, F, R, Y, E, C
4 | OPFKWIFS    | O, P, F, K, W, I, F, S

For update values of existing table use query:

update test6 A
  join (
        select id, group_concat(substr(lett,S.X,1) ORDER BY T.id, S.x SEPARATOR ', ') new
          from test6 T, seqnum S
         where S.X>0 and S.X<=length(T.lett)
         group by id
       ) B ON A.id=B.id
   set A.lett=B.new

Test on sqlfiddle.com

Maybe my question was wrong, but i dont have 4 rows. I have 50k rows and all with random letters. This is working, but i have no idea how to do this with every row. — Tauras, Aug 25 '16 at 09:45
@Tauras My answer should work on any number of rows. numbers in `seqnum` - is letter position on one string, not a count of input table rows — Mike, Aug 25 '16 at 09:54

How to add commas after every letter of random list of letters in database

2 Answers2