Link to home
Start Free TrialLog in
Avatar of bibabutze
bibabutze

asked on

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?

Avatar of elimesika
elimesika
Flag of Israel image

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 ..
Avatar of bibabutze
bibabutze

ASKER

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
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
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?
 
 
 
 
 
ASKER CERTIFIED SOLUTION
Avatar of elimesika
elimesika
Flag of Israel image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
One more addition

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


SET @destination = @destination + @source

Open in new window