Re: Split a Delimited String in SQL ( PROCEDURE split_string )
Sorry forgot to copy my version: It's not acepting the delimiter command, can be ? I'm really missing something here. +---+ | version() | +---+ | 4.0.24_Debian-10ubuntu2.3-log | +---+ mysql delimiter // - CREATE PROCEDURE simpleproc (OUT param1 INT) - BEGIN - - SELECT COUNT(*) INTO param1 FROM t; ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'delimiter // CREATE PROCEDURE simpleproc (OUT param1 INT) BEGIN On 6/21/06, listsql listsql [EMAIL PROTECTED] wrote: 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
Re: Split a Delimited String in SQL ( PROCEDURE split_string )
Definitivaly stopping and going for one or two cups of coffee. I was logged in to another mysql... when I copied the last email. Sorry guys, I don't want to add extra non-sense text to the list! +--+ | version()| +--+ | 5.0.22-Debian_1.dotdeb.1-log | +--+ Now delimiter works fine, but I still get error trying to create the procedure ( http://forge.mysql.com/snippets/view.php?id=4 ): ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE PROCEDURE split_string ( IN input TEXT, IN delimiter VARCHAR(10' at line 2 Just for the happy comment: Argentina Will win the match tonight against Holland :) MARTIN On 6/21/06, listsql listsql [EMAIL PROTECTED] wrote: Sorry forgot to copy my version: It's not acepting the delimiter command, can be ? I'm really missing something here. +---+ | version() | +---+ | 4.0.24_Debian-10ubuntu2.3-log | +---+ mysql delimiter // - CREATE PROCEDURE simpleproc (OUT param1 INT) - BEGIN - - SELECT COUNT(*) INTO param1 FROM t; ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'delimiter // CREATE PROCEDURE simpleproc (OUT param1 INT) BEGIN On 6/21/06, listsql listsql [EMAIL PROTECTED] wrote: 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
RE: Split a Delimited String in SQL ( PROCEDURE split_string )
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 invent --- -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]
Re: Split a Delimited String in SQL ( PROCEDURE split_string )
On Wednesday 21 June 2006 13:37, listsql listsql wrote: Just for the happy comment: Argentina Will win the match tonight against Holland :) MARTIN ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Argentina Will win the match tonight against Holland' at line 1 -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Split a Delimited String in SQL ( PROCEDURE split_string )
Hi, It's very important to have instaled the library client related to the MySQL server version.For example, you cannot use libmysql.dll version 3.X or 4.x to use stored procedure from version 5.X of MySQL RDBMS. Regards, _ G.NET SOFTWARE COMPANY SYSTEM INTEGRATOR - AUTOMATION SOFTWARE DEVELOPMENT http://www.gonetsoftware.com Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] -Original Message- From: Jørn Dahl-Stamnes [mailto:[EMAIL PROTECTED] Sent: Wednesday, 21 June, 2006 2:42 PM To: mysql@lists.mysql.com Subject: Re: Split a Delimited String in SQL ( PROCEDURE split_string ) On Wednesday 21 June 2006 13:37, listsql listsql wrote: Just for the happy comment: Argentina Will win the match tonight against Holland :) MARTIN ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Argentina Will win the match tonight against Holland' at line 1 -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
