Reputation: 167
I successfully created the following function in mysql
CREATE FUNCTION h2m
HTMLText(text)
RETURNS text
DETERMINISTIC
BEGIN
DECLARE Start INT;
DECLARE End INT;
DECLARE Length INT;
SET Start = CHARINDEX('<',HTMLText);
SET End = CHARINDEX('>',HTMLText,CHARINDEX('<',HTMLText));
SET Length = (End - Start) + 1;
WHILE Start > 0
AND End > 0
AND Length > 0
DO
SET HTMLText = STUFF(HTMLText,Start,Length,'');
SET Start = CHARINDEX('<',HTMLText);
SET End = CHARINDEX('>',HTMLText,CHARINDEX('<',HTMLText));
SET Length = (End - Start) + 1;
END WHILE;
RETURN LTRIM(RTRIM(HTMLText));
END;
Now when I'm trying to call it with the following code:
SELECT description h2m (HTMLText) FROM oc_product_description,
I am getting the following error:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use in '(HTML Text) FROM oc product_description LIMIT 0, 30' at line 2 –
Thank you so much for your help
Upvotes: 1
Views: 51
Reputation: 36709
You have two problems. First the CHARINDEX()
function isn't a built-in MySQL function. Unless you have it custom defined somewhere in your DB, you'll run into an error here. LOCATE()
is the built-in MySQL function that does something similar to what CHARINDEX()
does.
Your second problem is the way you're calling the function. You should be calling it in this fashion
SELECT h2m (description)
FROM oc_product_description
Upvotes: 1
Reputation: 1271231
You can try:
SELECT description, h2m(HTMLText)
FROM oc_product_description
Three things:
description
.h2m
and (
. MySQL has a problem with spaces after function names.oc_product_description
.EDIT:
Perhaps you want to pass description
in as the argument:
SELECT h2m(description)
FROM oc_product_description
Upvotes: 1