On May 5, 7:13*pm, toby <t...@telegraphics.com.au> wrote:
> On May 5, 3:17 pm, "cvh@LE" <christian.han...@cpi-service.com> wrote:
>
> > Hi all,
> > ...
> > Any hints or tips or reference I didnt find?
>
> RTFM would reveal SUBSTRING_INDEX():http://dev.mysql.com/doc/refman/5.0/....html#function...
>
> > TIA
>
> > Chris

RTFM??!? If you had read what I 've written you would have realised
that I am well aware of substring_index:

>mysql> SELECT MID('a,b,c,d',
> length(substring_index('a,b,c,d',",",3)),
> length(substring_index('a,b,c,d',",",4))-
>length(substring_index('a,b,c,d',",",3))-1
> ) complicated;

It rather seems to me that you dont know what a SET is! So RTFM to
you. If you dont have valueble input than just leave it to others to
give advice.

substring index is helpful function I do use to achieve the goal of
returning a string

As said , I solved the problem but am wondering if there does exist a
built-in function like FIND_IN_SET which takes a index as parameter
and not a string-element. I created my own function as follows :

CREATE FUNCTION element_in_set
(mystring varchar(4096),sep varchar(25), myindex int(4))
RETURNS varchar(4096) DETERMINISTIC
BEGIN
RETURN
replace(substr(mystring,length(substring_index(mys tring,sep,myindex))
+1,length(substring_index(mystring,sep,myindex+1))-
length(substring_index(mystring,sep,myindex))),sep ,"");
END
$$




mysql> -- Select the 5th (index=4) element from a space separated set
mysql> select element_in_set('So this is a sample set separated by
spaces'," ",4);
+---------------------------------------------------------------------
+
| element_in_set('So this is a sample set separated by spaces'," ",4)
|
+---------------------------------------------------------------------
+
| sample
|
+---------------------------------------------------------------------
+
1 row in set (0.00 sec)

mysql> -- finding the 7th ( index=6) element in aa comma separated set
mysql> select element_in_set('this,is,a,comma,separated,set,with ,
9,elements',",",6);
+-----------------------------------------------------------------------
+
| element_in_set('this,is,a,comma,separated,set,with ,9,elements',",",
6) |
+-----------------------------------------------------------------------
+
|
with
|
+-----------------------------------------------------------------------
+
1 row in set (0.00 sec)


So is there a built-in function?

@Jerry; Unfortunately this is not an option in this case. The database
is to be taken as is, but I am afraid I cant provide more information
on this. So just trust me on this.