List:General Discussion« Previous MessageNext Message »
From:listsql listsql Date:June 21 2006 11:27am
Subject:Re: Split a Delimited String in SQL ( PROCEDURE split_string )
View as plain text  
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 <listsql@stripped> 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
>
>

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