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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
elimesikaCommented:
One more addition

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


SET @destination = @destination + @source

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.