Retourne une partie de 1 à n; Si n> nombre de partie, renvoie null;
Autre source: http://blog.fedecarg.com/2009/02/22/mysql-split-string-function/
La fonction que je propose est moins performante 🙁 que le lien ci-dessus.
[code lang= »sql »]
DROP FUNCTION IF EXISTS SPLIT;
DELIMITER $$;
CREATE FUNCTION `SPLIT`( _text TEXT,_lim VARCHAR(255), _part INTEGER ) RETURNS text CHARSET utf8 COLLATE utf8_unicode_ci
BEGIN
DECLARE _substring TEXT;
DECLARE _index INTEGER ;
DECLARE _len_index INTEGER;
DECLARE _len_substring INTEGER;
DECLARE _len_lim INTEGER ;
set _part=_part-1;
set _len_lim=char_length(_lim)+1;
SET _len_index = LENGTH(_text) – LENGTH(REPLACE(_text, _lim,  »));
SET _index=0;

_indexwhile: WHILE _index <= _len_index DO
set _substring= SUBSTRING_INDEX(_text,_lim,1);
if( _part=_index) then
RETURN _substring;
END IF;
set _len_substring= char_length(_substring);
set _text=SUBSTR(_text,_len_substring+_len_lim,char_length(_text)-_len_substring);
SET _index = _index + 1;
END WHILE _indexwhile;
RETURN NULL;
END
[/code]

Pour la tester avec une procédure stocké:

[code lang= »sql »]DROP PROCEDURE IF EXISTS READ_SPLIT;
DELIMITER $$;
CREATE PROCEDURE `READ_SPLIT`( _text TEXT, _lim VARCHAR(255))
BEGIN
DECLARE _index INTEGER ;
DECLARE _len_index INTEGER;
SET _len_index = 1 + LENGTH(_text) – LENGTH(REPLACE(_text, _lim,  »));
SET _index=1;
CREATE TABLE IF NOT EXISTS RESULT(_text_result TEXT);
_indexwhile: WHILE _index <= _len_index DO
INSERT INTO RESULT ( _text_result) SELECT SPLIT(_text,_lim,_index);
SET _index = _index + 1;
END WHILE _indexwhile;
SELECT * FROM RESULT;
DROP TABLE RESULT;
END
[/code]
[code lang= »sql »]CALL READ_SPLIT("Hello World, this is a split function," ");
[/code]
Pour pousser l’histoire:
[code lang= »sql »]DROP PROCEDURE IF EXISTS LOOP_READ_SPLIT;
DELIMITER $$;
CREATE PROCEDURE `LOOP_READ_SPLIT`( _text TEXT, _lim VARCHAR(255),_len_index INTEGER)
BEGIN
DECLARE _index INTEGER ;
SET _index=1;
_indexwhile: WHILE _index <= _len_index DO
CALL READ_SPLIT(_text,_lim);
SET _index = _index + 1;
END WHILE _indexwhile;
END
[/code]
[code lang= »sql »]CALL LOOP_READ_SPLIT("Hello World, this is a split function," ",100);
[/code]