Tauras Tauras - 3 months ago 18
SQL Question

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

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

Answer

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.

Comments