0

Possible Duplicate:
Can you split/explode a field in a MySQL query?

I want to convert php explode function in mysql procedure
BUT
the explode function does not contain the length ( we do not know on which index does the delimiter comes )
AND
on the exploded array run a loop as in the php code I write for loop on that exploded array

PHP Code

$data = 'Computers|Personal Computer|Home Use|Accessories|Mouse|Wireless Mouse';    

$categories = explode("|", $data);

for ( $i = 0; $i < sizeof($categories); $i++ ){

      $this->insert_category( $categories[$i] );
}

I used the function below in mysql but it only returns a single value but I want an array also this function wants a specfic index which in my case is not known because data will be of any type.
Mysql 

CREATE FUNCTION SPLIT_STR(  
   x VARCHAR(255),  
   delim VARCHAR(12),  
   pos INT  
 )


RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),  
   LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),  
   delim, '');

I have also used these function just to make some thing closer to my result but the functions are not behaving accurately e.g they are not spliting correctly, not returning correct index etc,

i am using all the functions below one by one or with a combination not all at the same time 

SET temp2 = substring_index(data_mysql, '|', 2);  
SET temp2 = find_in_set('|', data_mysql);  

SET temp2 = instr(data_mysql, '|');  
SET temp3 = SPLIT_STR(data_mysql, '|', temp2 );

I declared the variables in the mysql procedure as 

DECLARE data_mysql           VARCHAR(200) DEFAULT NULL;  
DECLARE temp1                VARCHAR(150) DEFAULT NULL;  
DECLARE temp2                VARCHAR(150) DEFAULT NULL;  
DECLARE temp3                VARCHAR(150) DEFAULT NULL;
Community
  • 1
  • 1
user1547567
  • 1
  • 1
  • WHy do you wish to do this in the database, and not in code? – Nanne Jul 24 '12 at 05:59
  • Also, you might just want to have a look-see over at the questions about this allready on the site, like http://stackoverflow.com/questions/471914/can-you-split-explode-a-field-in-a-mysql-query – Nanne Jul 24 '12 at 06:00
  • @Nanne 1: Because browser stop after 5 min and the script execution time is of more than an hour.(i have also tried all the solutions of increasing the time from php.ini but all in vain ) 2:This is not the exect solution – user1547567 Jul 24 '12 at 06:06
  • Consider the return type of your function: MySQL doesn't have an array [type](http://dev.mysql.com/doc/en/data-types.html), so how will you return the split string? One approach is to store it in a temporary table; another is to have a function that returns the *n* th item in the string, where *n* is provided as a parameter. Plenty of examples of both are available online. – eggyal Jul 24 '12 at 06:06
  • I'm not really sure that moving a 'split' from code to sql will help you get an execution time of an hour to something around 5 minutes. – Nanne Jul 24 '12 at 06:09
  • @Nanne : the records are more than a 1 billion to explode and store in db that is why I come to mysql procedure because there is no limit of procedure execution – user1547567 Jul 24 '12 at 06:12

0 Answers0