Solved

Create function in MYSQL

Posted on 2010-09-02
16
732 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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

708 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

20 Experts available now in Live!

Get 1:1 Help Now