Solved

Create function in MYSQL

Posted on 2010-09-02
16
737 Views
Last Modified: 2012-05-10
I have a great need to create a function that will remove html tags in a MYSQL database.  I have a function that works in SQL Server, but I cannot get it to work in MYSQL.  I suspect it is a syntax issue, but can't for the life of me figure it out.  I am also brand new at creating functions, so I will tell you all the gory details so I make no assumptions.

This is what I have done - in SQL Server, I created a view and pasted it in the following code.  It created my function:
CREATE FUNCTION [dbo].[udf_StripHTML]
(@HTMLText VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
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
BEGIN
SET @HTMLText = STUFF(@HTMLText,@Start,@Length,'')
SET @Start = CHARINDEX('<',@HTMLText)
SET @End = CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText))
SET @Length = (@End - @Start) + 1
END
RETURN LTRIM(RTRIM(@HTMLText))
END

Then I created a view with this code:

SELECT dbo.udf_StripHTML('<b>UDF at SQLAuthority.com </b><br><br><a href="http://www.SQLAuthority.com">SQLAuthority.com</a>')

which returned just what I wanted:

UDF at SQLAuthority.com SQLAuthority.com

So far, so good.

Now I am trying to achieve the same thing in a MYSQL application called Navicat, which is new to me.  When I attempt to create the same function as above and save it, I get the error message:

[syntax error]
near '[dbo].[udf_StripHTML]
(@HTMLTextVARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN' at line 3


Can you please identify what is wrong.  My second issue is one of guidance.  If we can get this to save and run, I need it to read data in a MYSQL field and return the result set.  I'm not sure how to get it to look at the table.  Is it a matter of another view with a select statement, such as:

SELECT dbo.udf_StripHTML(tablename.fieldname)

I truly appreciate any help any of you can provide.  Thank you so much.
0
Comment
Question by:ginsburg7
  • 8
  • 4
  • 4
16 Comments
 
LVL 26

Expert Comment

by:ushastry
ID: 33598707
Hi!!

Try this function..which would do as expected...  

Taken from MySQL Forums..
DROP FUNCTION IF EXISTS fnStripTags; 

DELIMITER | 

CREATE FUNCTION fnStripTags( Dirty varchar(4000) ) 

RETURNS varchar(4000) 

DETERMINISTIC  

BEGIN 

  DECLARE iStart, iEnd, iLength int; 

  WHILE Locate( '<', Dirty ) > 0 And Locate( '>', Dirty, Locate( '<', Dirty )) > 0 DO 

    BEGIN 

      SET iStart = Locate( '<', Dirty ), iEnd = Locate( '>', Dirty, Locate('<', Dirty )); 

      SET iLength = ( iEnd - iStart) + 1; 

      IF iLength > 0 THEN 

        BEGIN 

          SET Dirty = Insert( Dirty, iStart, iLength, ''); 

        END; 

      END IF; 

    END; 

  END WHILE; 

  RETURN Dirty; 

END; 

| 

DELIMITER ; 





### Usage..



SELECT fnStripTags('<b>UDF at SQLAuthority.com </b><br><br><a href="http://www.SQLAuthority.com">SQLAuthority.com</a>');



=============================

UDF at SQLAuthority.com SQLAuthority.com

Open in new window

0
 

Author Comment

by:ginsburg7
ID: 33598825
It returns this error statement:
[Err] 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 near '|
DELIMITER' at line 1

Thank you for trying.  Do you have another suggestion for the error?
0
 
LVL 26

Expert Comment

by:ushastry
ID: 33598874
Pls post the output of below SQL command...

select version();
0
 

Author Comment

by:ginsburg7
ID: 33599023
This is what I get in the message box.  Then I tried it again without the SELECT statement at the end, just to create the function, and I got the same message:
[SQL] DROP FUNCTION IF EXISTS fnStripTags;
Affected rows: 0
Time: 0.261ms

[SQL]  

CREATE FUNCTION fnStripTags( Dirty varchar(4000) )
RETURNS varchar(4000)
DETERMINISTIC  
BEGIN
  DECLARE iStart, iEnd, iLength int;
  WHILE Locate( '<', Dirty ) > 0 And Locate( '>', Dirty, Locate( '<', Dirty )) > 0 DO
    BEGIN
      SET iStart = Locate( '<', Dirty ), iEnd = Locate( '>', Dirty, Locate('<', Dirty ));
      SET iLength = ( iEnd - iStart) + 1;
      IF iLength > 0 THEN
        BEGIN
          SET Dirty = Insert( Dirty, iStart, iLength, '');
        END;
      END IF;
    END;
  END WHILE;
  RETURN Dirty;
END;
|
DELIMITER ;


### Usage..

SELECT fnStripTags('<b>UDF at SQLAuthority.com </b><br><br><a href="http://www.SQLAuthority.com">SQLAuthority.com</a>');

[Err] 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 near '|
DELIMITER ;
0
 
LVL 26

Expert Comment

by:ushastry
ID: 33599048
Pls let me know your MySQL version.. no need include below SQL command with function...

select version();
0
 

Author Comment

by:ginsburg7
ID: 33599107
The application is Navicat 9.0.12, but I'm not sure if I am answering your question.  I don't know how to tell what version of MYSQL it is running on.
0
 
LVL 26

Expert Comment

by:ushastry
ID: 33599167
Pls execute below SQL command... it would tell you the MySQL version.. just want to see if you ahve right version of MySQL

select version();
0
 

Author Comment

by:ginsburg7
ID: 33599526
Oh, I didn't realize that's what you meant.  Sorry.  I have 5.0.91-community.
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 26

Expert Comment

by:Zberteoc
ID: 33600845
Create the function bellow and then test with:

set @udf='<b>UDF at SQLAuthority.com </b><br><br><a href="http://www.SQLAuthority.com">SQLAuthority.com</a>';

select udf_StripHTML(@udf) str;
delimiter $$



DROP FUNCTION IF EXISTS `udf_StripHTML`$$



CREATE FUNCTION `udf_StripHTML`(_HTMLText VARCHAR(8000)) RETURNS varchar(8000)

    DETERMINISTIC

BEGIN

	set @HTMLText=_HTMLText;

	

	while locate('<',@HTMLText)>0 do 

	begin

		set 

			@pos_lt=locate('<',@HTMLText),

			@pos_gt=locate('>',@HTMLText);

		set 

			@HTMLText=replace(@HTMLText,substring(@HTMLText,@pos_lt,@pos_gt),'');

	end;

	end while;

	RETURN @HTMLText;

END$$



delimiter ;

Open in new window

0
 

Author Comment

by:ginsburg7
ID: 33600893
I got this error message:


[Err] 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 near 'delimiter' at line 18

Line 18 is the
end while;
line
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 33601968
It might be your software. I am using SQLyog and worked just fine.

Let's try below:
DROP FUNCTION IF EXISTS `udf_StripHTML`;



CREATE FUNCTION `udf_StripHTML`(_HTMLText VARCHAR(8000)) RETURNS varchar(8000)

    DETERMINISTIC

BEGIN

	set @HTMLText=_HTMLText;

	

	while locate('<',@HTMLText)>0 do 

	begin

		set 

			@pos_lt=locate('<',@HTMLText),

			@pos_gt=locate('>',@HTMLText);

		set 

			@HTMLText=replace(@HTMLText,substring(@HTMLText,@pos_lt,@pos_gt),'');

	end;

	end while;

	RETURN @HTMLText;

END;

Open in new window

0
 

Author Comment

by:ginsburg7
ID: 33601977
That seems to have run.  What did it do, and what next?  Thank you so much!
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 33601981
Now you just use the function exactly as you would with SQL server:

set @udf='<b>UDF at SQLAuthority.com </b><br><br><a href="http://www.SQLAuthority.com">SQLAuthority.com</a>';

select udf_StripHTML(@udf) str;
0
 

Author Comment

by:ginsburg7
ID: 33601988
this is the next part that I am confused about.  I want to run this function against a database field, such as table.field.

Do I do this as a SELECT statement, and if so, how, please.  I greatly appreciate your assistance!!
0
 
LVL 26

Accepted Solution

by:
Zberteoc earned 500 total points
ID: 33602045
lets say you have a table called tblurl with a column as urlcol with your html tags. If you want to apply th efunction to the column just do:

select udf_StripHTML(urlcol) as stripHTML from tblurl;

0
 

Author Closing Comment

by:ginsburg7
ID: 33602060
WOW!  I think you did it!!!  What did you do that the others couldn't do?  I wish I could give you a million points!  Thank you so much!
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now