• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 487
  • Last Modified:

Is it possible to decode html text on MySQL ?

Hi.

I have a MySQL table with a description column. This description contains rich text and is stored in html format.
To search text purposes, I have added another field which I want to contain the plain text version of this html text, so I will create a trigger that fills/updates this new field when a row is inserted or when the html field is updated.

My question is how can I decode the HTML. I have found a code to create a function to strip tags, but I don't know how can I convert html special characters to text (for example UTF-8 encoded).  I know this is easy in php but I need to do it on mysql trigger so I need a mysql way to made this conversion.

Is this possible ?

Thank you.
0
gplana
Asked:
gplana
  • 5
  • 4
1 Solution
 
NorieVBA ExpertCommented:
How does the data get into the table?

Couldn't you strip the HTML when that's done?
0
 
gplanaAuthor Commented:
Data is inserted and updated from a third-party Wordpress plugin. I know we can do it on php if we controll when and where data is inserted or updated, but this is not the case. Also some other added systems can update the html field, even directly from phpMyAdmin. This is why we want to implement on MySQL side.
So is it possible to do it on MySQL ? Is it possible to convert html in text inside a MySQL trigger ?

Thank you.
0
 
NorieVBA ExpertCommented:
to strip the HTML you probably need to a user defined function.

There's an example here http://stackoverflow.com/questions/2627940/remove-html-tags-from-record.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
gplanaAuthor Commented:
Thank you, but as I said before, I have already found how to strip html tags. What I want is to decode html chars like á. I know I can do this by using string replace functions, but I feel there are too many html special chars for doing this way, so I wonder if there is another way.

0
 
NorieVBA ExpertCommented:
Do you have a table with the codes for the special characters and their replacements?
0
 
gplanaAuthor Commented:
No, I haven't. However, it could be a good idea.
0
 
NorieVBA ExpertCommented:
I think it might be the only idea.

It would be easy enough to strip the special characters, but replacing them is a different matter.

I've got a list of some of them (254) in Excel if you want it but I'm not sure about the UTF-8 encoding you mention - not used that much.
0
 
gplanaAuthor Commented:
I think this list will be very useful. Can you attach it or let me know how can I get it ?
0
 
NorieVBA ExpertCommented:
Here it is.

Don't know how reliable it is - as reliable as Wikipedia I suppose, that's where I got it.:)



HTMLSpecialCharCodes.xls
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now