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?
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?
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
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
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
One more addition
after the loop , add this (to catch the last word)
after the loop , add this (to catch the last word)
SET @destination = @destination + @source
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 ..