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.
LVL 15
gplanaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
How does the data get into the table?

Couldn't you strip the HTML when that's done?
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.
NorieAnalyst Assistant Commented:
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.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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.

NorieAnalyst Assistant Commented:
Do you have a table with the codes for the special characters and their replacements?
gplanaAuthor Commented:
No, I haven't. However, it could be a good idea.
NorieAnalyst Assistant Commented:
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.
gplanaAuthor Commented:
I think this list will be very useful. Can you attach it or let me know how can I get it ?
NorieAnalyst Assistant Commented:
Here it is.

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



HTMLSpecialCharCodes.xls

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.