From: Date: June 21 2006 1:40pm
Subject: RE: Split a Delimited String in SQL ( PROCEDURE split_string )
List-Archive: http://lists.mysql.com/mysql/199134
Message-Id: <2E5067E0651BCD4FB7133D3F8B854FEB21AE5B@NWIEXC01.asiapacific.cpqcorp.net>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

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=3DMEMORY;

      SET remainder =3D input;
      SET delimiter_length =3D CHAR_LENGTH(delimiter);

      WHILE CHAR_LENGTH(remainder) > 0 AND cur_position > 0 DO
         SET cur_position =3D INSTR(remainder, delimiter);

         IF cur_position =3D 0 THEN
            SET cur_string =3D remainder;
         ELSE
            SET cur_string =3D LEFT(remainder, cur_position - 1);
         END IF;

         IF TRIM(cur_string) !=3D '' THEN
            INSERT INTO SplitValues VALUES (cur_string);
         END IF;

         SET remainder =3D SUBSTRING(remainder, cur_position +
delimiter_length);
      END WHILE;
   END;
//

Regards

---------------------------------------------------------------
********** _/     **********  David Logan=20
*******   _/         *******  ITO Delivery Specialist - Database
*****    _/            *****  Hewlett-Packard Australia Ltd
****    _/_/_/  _/_/_/  ****  E-Mail: david.logan@stripped
****   _/  _/  _/  _/   ****  Desk:   +618 8408 4273
****  _/  _/  _/_/_/    ****  Mobile: 0417 268 665
*****        _/       ******   =20
******      _/      ********  Postal: 148 Frome Street,
********   _/     **********          Adelaide SA 5001
                                      Australia=20
i    n    v    e    n    t                                  =20
---------------------------------------------------------------

-----Original Message-----
From: listsql listsql [mailto:listsql@stripped]=20
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=3D4

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=3DMEMORY;
      SET remainder =3D input;
      SET delimiter_length =3D CHAR_LENGTH(delimiter);

       WHILE CHAR_LENGTH(remainder) > 0 AND cur_position > 0 DO
        SET cur_position =3D INSTR(remainder, delimiter);

      IF cur_position =3D 0 THEN
        SET cur_string =3D remainder;
     ELSE
        SET cur_string =3D LEFT(remainder, cur_position - 1);
        END IF;

      IF TRIM(cur_string) !=3D '' THEN
        INSERT INTO SplitValues VALUES (cur_string);
     END IF;

      SET remainder =3D SUBSTRING(remainder, cur_position +
delimiter_length);

      END WHILE;
      END

__________________________________________________--

Best regards,
MARTIN