List:General Discussion« Previous MessageNext Message »
From:David (SST - Adelaide) Logan Date:June 21 2006 11:40am
Subject:RE: Split a Delimited String in SQL ( PROCEDURE split_string )
View as plain text  
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: david.logan@stripped
****   _/  _/  _/  _/   ****  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:listsql@stripped] 
Sent: Wednesday, 21 June 2006 8:36 PM
To: mysql@stripped
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
Thread
Split a Delimited String in SQL ( PROCEDURE split_string )listsql listsql21 Jun
  • Re: Split a Delimited String in SQL ( PROCEDURE split_string )listsql listsql21 Jun
    • Re: Split a Delimited String in SQL ( PROCEDURE split_string )listsql listsql21 Jun
      • Re: Split a Delimited String in SQL ( PROCEDURE split_string )Jørn Dahl-Stamnes21 Jun
  • RE: Split a Delimited String in SQL ( PROCEDURE split_string )SST - Adelaide)21 Jun
RE: Split a Delimited String in SQL ( PROCEDURE split_string )Gelu Gogancea21 Jun