Hi, There are a couple of errors in the way it is defined, firstly
1) There should be a ; after the DROP PROCEDURE statement 2) use a delimiter //, this stops the mysql client trying to interpret the other ; as the end of the command. It then takes everything until the next // as belonging to the PROCEDURE. 3) it also needs a ; after the final END 4) Will object if you don't have a database selected already as it is trying to create a temporary table. eg. DROP PROCEDURE IF EXISTS split_string; delimiter // CREATE PROCEDURE split_string ( IN input TEXT, IN delimiter VARCHAR(10) ) SQL SECURITY INVOKER BEGIN DECLARE cur_position INT DEFAULT 1 ; DECLARE remainder TEXT; DECLARE cur_string VARCHAR(1000); DECLARE delimiter_length TINYINT UNSIGNED; DROP TEMPORARY TABLE IF EXISTS SplitValues; CREATE TEMPORARY TABLE SplitValues ( value VARCHAR(1000) NOT NULL PRIMARY KEY ) ENGINE=MEMORY; SET remainder = input; SET delimiter_length = CHAR_LENGTH(delimiter); WHILE CHAR_LENGTH(remainder) > 0 AND cur_position > 0 DO SET cur_position = INSTR(remainder, delimiter); IF cur_position = 0 THEN SET cur_string = remainder; ELSE SET cur_string = LEFT(remainder, cur_position - 1); END IF; IF TRIM(cur_string) != '' THEN INSERT INTO SplitValues VALUES (cur_string); END IF; SET remainder = SUBSTRING(remainder, cur_position + delimiter_length); END WHILE; END; // Regards --------------------------------------------------------------- ********** _/ ********** David Logan ******* _/ ******* ITO Delivery Specialist - Database ***** _/ ***** Hewlett-Packard Australia Ltd **** _/_/_/ _/_/_/ **** E-Mail: [EMAIL PROTECTED] **** _/ _/ _/ _/ **** Desk: +618 8408 4273 **** _/ _/ _/_/_/ **** Mobile: 0417 268 665 ***** _/ ****** ****** _/ ******** Postal: 148 Frome Street, ******** _/ ********** Adelaide SA 5001 Australia i n v e n t --------------------------------------------------------------- -----Original Message----- From: listsql listsql [mailto:[EMAIL PROTECTED] Sent: Wednesday, 21 June 2006 8:36 PM To: mysql@lists.mysql.com Subject: Split a Delimited String in SQL ( PROCEDURE split_string ) Hi all, I was trying this: http://forge.mysql.com/snippets/view.php?id=4 That is supposed to emulate a split() in mysql. Could anyone make it work ? I've been trying without luck. I 'm getting strange errors when trying to create this procedure. _____________________________________ DROP PROCEDURE IF EXISTS split_string CREATE PROCEDURE split_string ( IN input TEXT, IN delimiter VARCHAR(10) ) SQL SECURITY INVOKER BEGIN DECLARE cur_position INT DEFAULT 1 ; DECLARE remainder TEXT; DECLARE cur_string VARCHAR(1000); DECLARE delimiter_length TINYINT UNSIGNED; DROP TEMPORARY TABLE IF EXISTS SplitValues; CREATE TEMPORARY TABLE SplitValues ( value VARCHAR(1000) NOT NULL PRIMARY KEY ) ENGINE=MEMORY; SET remainder = input; SET delimiter_length = CHAR_LENGTH(delimiter); WHILE CHAR_LENGTH(remainder) > 0 AND cur_position > 0 DO SET cur_position = INSTR(remainder, delimiter); IF cur_position = 0 THEN SET cur_string = remainder; ELSE SET cur_string = LEFT(remainder, cur_position - 1); END IF; IF TRIM(cur_string) != '' THEN INSERT INTO SplitValues VALUES (cur_string); END IF; SET remainder = SUBSTRING(remainder, cur_position + delimiter_length); END WHILE; END __________________________________________________-- Best regards, MARTIN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]