Solved

Create function in MYSQL

Posted on 2010-09-02
16
744 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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
 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…

749 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