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: [email protected]
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]
