MySql Field Substrings

Question

I'm making one MySql script that break one string field in multiple words.

I need something like the explode function in PHP!

I try the mysql substring_index function but I have to expecify the number of occurences of the one substring. And that is not predictable in one 10.000 row table.

Any sugestion?

this is my actual Stored Procedure:

**CREATE PROCEDURE `extract_words` ()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE result_row CHAR(100);
DECLARE cursor_1 CURSOR FOR SELECT description FROM db_movies;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;


OPEN cursor_1;


REPEAT
  FETCH cursor_1 INTO result_row;
  IF NOT done THEN
    INSERT INTO extrator_words (word) VALUES (result_row);
  END IF;
UNTIL done END REPEAT;


CLOSE cursor_1;

END $$**

Thanks,
Pedro
@pcamacho

I doubt this is the solution to your actual problem. What are you trying to accomplish in the first place? — soulmerge
– soulmerge, Commented Aug 20, 2009 at 15:34
I want to create one table with all words in the description field, to generate some SEO Reports, But I'm trying to do that all in Database, without using scripts and in the minimum possible number of Queries — Pedro
– Pedro, Commented Aug 20, 2009 at 15:38

Zed · Accepted Answer · 2009-08-20 16:54:08Z

1

...
SET @currText = textToExpolde;
SET @sepLen   = LENGTH( separator );

WHILE @currText != '' DO

  SET @word     = SUBSTRING_INDEX(@currText, separator, 1);
  SET @currText = SUBSTRING(@currText, LENGTH(@word) + 1 + @sepLen);

  INSERT INTO extractor_words SET word = @word;

END WHILE;
...

edited Aug 20, 2009 at 16:54

answered Aug 20, 2009 at 15:33

Zed

57.8k9 gold badges79 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Zed Over a year ago

Sorry, that was a typo, that should be currText as well (fixed) =)

Pedro Over a year ago

Thank You Very MUCH!It work but you have small error instead of SET @currText = SUBSTRING(@word, LENGTH(@word) + 1 + @sepLen); is: SET @currText = SUBSTRING(@currText, LENGTH(@word) + 1 + @sepLen);

Collectives™ on Stack Overflow

MySql Field Substrings

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related