cvh@LE wrote:
> 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.
>

Like I said, there is a way to do it. Normalize your database. Your
table violates first normal form.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================