mysql udf to remove words from string less than x chars

Hi experts,
does anybody know a mysql user defined function that can be used for a field to return only words which have more than 3 or 4 chars?

e.G.
when i have the followning string in my column x and want to have all the words > 3 chars
"Harry Potter und der Orden des Phönix" should retrurn
"Harry Potter Orden Phönix"

how can i achive this?

bibabutzeAsked:
Who is Participating?
 
elimesikaCommented:
This is unchecked code , @source is the original string @destination is the one you are looking for ....

SET @source = 'Harry Potter und der Orden des Phönix';
SET @destination = '';
SET @i = SELECT INSTR(@source , ' ');
WHILE @i > 0 DO
     SET @next = LEFT(@source ,@i - 1)
     if LENGTH(@next) > 3
         SET @destination = @destination + @next + ' '
     SET @source = SUBSTRING(@source,@i + 1);
     SET @i = SELECT INSTR(@source , ' ');
END WHILE;

Open in new window

0
 
elimesikaCommented:
HI

SQL is not recomanded for string manipulations and if you implement it in the SQL engine, you may suffer from performance peroblems, therefor, I recommend that you will get the whole string and manipulate it in your business layer code ..
0
 
bibabutzeAuthor Commented:
i can do it only at mysql. It will be a part of a maintenance job which only runs once a day at night time to create a new table. so performance should not be the issue
0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
elimesikaCommented:
OK
Sorry for not having the time to write it for you , but here are the directions:

1) You can do that only in cursor when you fetch value by value from the table into a variable and manipulate it.
See http://www.kbedell.com/2009/03/02/a-simple-example-of-a-mysql-stored-procedure-that-uses-a-cursor/

2) In the cursor loop you should use string manipulation functions to do your job , basically INSTR() to find the next space , SUBSTR() to get the a word and  LENGTH() to check the word length and decide if to add it to a string result variable that you should print per table row.
See http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
0
 
bibabutzeAuthor Commented:
do you have maybe a small example for that. Or how can i loop in a string within the function to do your no. 2?
 
 
 
 
 
0
 
elimesikaCommented:
One more addition

after the loop , add this (to catch the last word)


SET @destination = @destination + @source

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.