Error when Running a function in MySQL

Question

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

Lloyd Banks · Accepted Answer · 2015-04-06 00:53:07Z

1

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

answered Apr 6, 2015 at 0:53

Lloyd Banks

36.9k58 gold badges172 silver badges264 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Schwann Over a year ago

Everything you mentioned worked like a charm, Would you please give me a command to execute the function that I have stored? the command I have right now only selects them. This function is suppose to search description column and convert html codes to text by eliminating the html tags.

Lloyd Banks Over a year ago

@Schwann I am not sure I understand your question. The command you have right now is already searching and converting your HTML

Schwann Over a year ago

@Liyod, I did use that command, but it did not update and change the column, it just selected and gave me a list.

Gordon Linoff · Accepted Answer · 2015-04-06 00:46:30Z

1

You can try:

SELECT description, h2m(HTMLText)
FROM oc_product_description

Three things:

Comma after description.
Removing space between h2m and (. MySQL has a problem with spaces after function names.
Removal of comma after oc_product_description.

EDIT:

Perhaps you want to pass description in as the argument:

SELECT h2m(description)
FROM oc_product_description

edited Apr 6, 2015 at 0:46

answered Apr 6, 2015 at 0:41

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

2 Comments

Schwann Over a year ago

Thank you @Gordon Linoff, I ran yours and It gives me unknown column HTMLText error

Schwann Over a year ago

Now it says, Commands out of sync, you cannot run this command now!

Collectives™ on Stack Overflow

Error when Running a function in MySQL

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related