MySQL function to HTMLDecode a text value

HTMLDecode.sql

CREATE FUNCTION `HTMLDecode`(x TEXT) RETURNS text CHARSET latin1 
BEGIN

DECLARE TextString TEXT;
SET TextString = x; 

SET TextString = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TextString,"<strong>", ""),"</strong>", ""),"<p>", ""),"</p>", "\n"),"<ul>", ""),"<li>", "- "),"</li>", "\n"),"</ul>", ""),"<br />", "\n"),"<br>", "\n"),"&amp;", "&"),"&nbsp;", " "),"&#39;", "'"),"&lt;", "<"),"&gt;", ">"),"&ndash;", "-"),"&bull;", "- "),"&rsquo;", "'"),"&hellip;", "..."),"&middot;", "- "),"&rdquo;", "'"),"&ldquo;", "'"),"&lsquo;", "'"),"&#x23;", "#"),"&frac12;", "1/2"),"&deg;", "°"),"<i>", ""),"</i>", ""),"&quot;",'"'),"&apos;",'"')

RETURN TextString; 

END

TinkerCode.php

$replacements = [
    "<strong>" => "",
    "</strong>" => "",
    "<p>" => "",
    "</p>" => "\\n",
    "<ul>" => "",
    "<li>" => "- ",
    "</li>" => "\\n",
    "</ul>" => "",
    "<br />" => "\\n",
    "<br>" => "\\n",
    "&amp;" => "&",
    "&nbsp;" => " ",
    "&#39;" => "'",
    "&lt;" => "<",
    "&gt;" => ">",
    "&ndash;" => "-",
    "&bull;" => "- ",
    "&rsquo;" => "'",
  "&hellip;"=>"...",
  "&middot;"=>"- ",
  "&rdquo;"=>"'",
  "&ldquo;"=>"'",
  "&lsquo;"=>"'",
  "&#x23;"=>"#",
  "&frac12;"=>"1/2",
  "&deg;"=>"°",
  "<i>"=>"",
  "</i>"=>"",
  
];

$level = 1;
$rep_str = "TextString";
foreach ($replacements as $a => $b) {
    $rep_str = "REPLACE(" . $rep_str . ',"' . $a . '", "' . $b . '")';
}

$rep_str = "REPLACE(" . $rep_str . ',"&quot;",\'"\')';
$rep_str = "REPLACE(" . $rep_str . ',"&apos;",\'"\')';

      
echo "CREATE FUNCTION `HTMLDecode`(x TEXT) RETURNS text CHARSET latin1 \n";
echo "BEGIN\n\n";
echo "DECLARE TextString TEXT;\n";
echo "SET TextString = x; \n\n";
echo "SET TextString = " . $rep_str . "\n\n";
echo "RETURN TextString; \n\n";
echo "END";

I use Tinkerwell (a Laravel tool to execute PHP code really well) (https://tinkerwell.app/)

I found myself losing track of all the REPLACE tags, so in order to better organize the replacements, I threw them into a php array and then created the create function statement dynamically. basically the TinkerCode.php file is what you need to execute in PHP to change up your replacements/add/remove/edit etc...

I have to give credit to Charlie Armor (here: https://forums.mysql.com/read.php?98,246527,246527 ) for the original code. I found that doing all the replace statements without testing if the string contents exist in the string first had much better performance.