Re: How function can help

[Roland Bouman]

mysql> select substring(substring_index(@v1:='what,fun,no?',',',@n:=1),character_length(substring_index(@v1,',',@n-1))+1+if(@n=1,0,1)) c1
-> , substring(substring_index(@v1,',',@n:=@n+1),character_length(substring_index(@v1,',',@n-1))+1+if(@n=1,0,1)) c2
-> , substring(substring_index(@v1,',',@n:=@n+1),character_length(substring_index(@v1,',',@n-1))+1+if(@n=1,0,1)) c3
-> ;
+------+------+------+
| c1 | c2 | c3 |
+------+------+------+
| what | fun | no? |
+------+------+------+
1 row in set (0.00 sec)

BTW not the most efficient probably, but the fun is that you only need to think of just two things, regardless of how many of these expressions you want:

1) in the first expression, be sure to enter your string. I'm talking about this part of the first expression:

@v1:='what,fun,no?'

2) again in the first expression, be sure to enter a 1 for @n:

@n:=1

So, apart from the

substring_index(@v1:='what,fun,no?',',',@n:=1)

from the first column, the remaining expressions are completely identical. You could just as well have had a string with 4,5,..N comma separated values, and you'd still be able to add columns add will; all getting exactly the value you need:

mysql> select substring(substring_index(@v1:='yes,this,really,is,fun',',',@n:=1),character_length(substring_index(@v1,',',@n-1))+1+if(@n=1,0,1)) c1
-> , substring(substring_index(@v1,',',@n:=@n+1),character_length(substring_index(@v1,',',@n-1))+1+if(@n=1,0,1)) c2
-> , substring(substring_index(@v1,',',@n:=@n+1),character_length(substring_index(@v1,',',@n-1))+1+if(@n=1,0,1)) c3
-> , substring(substring_index(@v1,',',@n:=@n+1),character_length(substring_index(@v1,',',@n-1))+1+if(@n=1,0,1)) c4
-> , substring(substring_index(@v1,',',@n:=@n+1),character_length(substring_index(@v1,',',@n-1))+1+if(@n=1,0,1)) c5
-> ;
+------+------+--------+------+------+
| c1 | c2 | c3 | c4 | c5 |
+------+------+--------+------+------+
| yes | this | really | is | fun |
+------+------+--------+------+------+
1 row in set (0.01 sec)



Edited 1 time(s). Last edit at 11/24/2005 07:53PM by Roland Bouman.