MySQL search and replace unicode (hexidecimal) characters with HTML entities

Not really pretty, but this will replace Latin-1 Supplement and Latin Extended-A characters (plus permutations of quotes, dashes and apostrophes) with their corresponding HTML entities. NOTE: if you use this, copy the queries from the source code of this page, the browser is translating the entities. Query is operating on a column called DESCRIPTION.

UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00A0'),' ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00A1'),'¡');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00A2'),'¢');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00A3'),'£');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00A4'),'¤');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00A5'),'¥');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00A6'),'¦');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00A7'),'§');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00A8'),'¨');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00A9'),'©');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00AA'),'ª');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00AB'),'«');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00AC'),'¬');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00AD'),'­');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00AE'),'®');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00AF'),'¯');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00B0'),'°');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00B1'),'±');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00B2'),'²');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00B3'),'³');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00B4'),'´');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00B5'),'µ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00B6'),'¶');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00B7'),'·');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00B8'),'¸');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00B9'),'¹');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00BA'),'º');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00BB'),'»');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00BC'),'¼');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00BD'),'½');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00BE'),'¾');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00BF'),'¿');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00C0'),'À');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00C1'),'Á');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00C2'),'Â');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00C3'),'Ã');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00C4'),'Ä');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00C5'),'Å');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00C6'),'Æ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00C7'),'Ç');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00C8'),'È');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00C9'),'É');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00CA'),'Ê');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00CB'),'Ë');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00CC'),'Ì');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00CD'),'Í');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00CE'),'Î');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00CF'),'Ï');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00D0'),'Ð');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00D1'),'Ñ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00D2'),'Ò');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00D3'),'Ó');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00D4'),'Ô');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00D5'),'Õ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00D6'),'Ö');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00D9'),'Ù');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00DA'),'Ú');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00DB'),'Û');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00DC'),'Ü');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00DD'),'Ý');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00DE'),'Þ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00DF'),'ß');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00E0'),'à');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00E1'),'á');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00E2'),'â');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00E3'),'ã');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00E4'),'ä');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00E5'),'å');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00E6'),'æ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00E7'),'ç');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00E8'),'è');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00E9'),'é');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00EA'),'ê');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00EB'),'ë');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00EC'),'ì');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00ED'),'í');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00EE'),'î');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00EF'),'ï');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00F0'),'ð');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00F1'),'ñ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00F2'),'ò');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00F3'),'ó');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00F4'),'ô');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00F5'),'õ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00F6'),'ö');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00F7'),'÷');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00F8'),'ø');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00F9'),'ù');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00FA'),'ú');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00FB'),'û');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00FC'),'ü');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00FD'),'ý');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00FE'),'þ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0100'),'Ā');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0101'),'ā');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0102'),'Ă');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0103'),'ă');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0104'),'Ą');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0105'),'ą');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0106'),'Ć');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0107'),'ć');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0108'),'Ĉ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0109'),'ĉ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'010A'),'Ċ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'010B'),'ċ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'010C'),'Č');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'010D'),'č');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'010E'),'Ď');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'010F'),'ď');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0110'),'Đ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0111'),'đ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0112'),'Ē');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0113'),'ē');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0114'),'&Ebreve;');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0115'),'&ebreve;');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0116'),'Ė');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0117'),'ė');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0118'),'Ę');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0119'),'ę');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'011A'),'Ě');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'011B'),'ě');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'011C'),'Ĝ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'011D'),'ĝ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'011E'),'Ğ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'011F'),'ğ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0120'),'Ġ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0121'),'ġ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0122'),'Ģ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0124'),'Ĥ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0125'),'ĥ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0126'),'Ħ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0127'),'ħ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0128'),'Ĩ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0129'),'ĩ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'012A'),'Ī');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'012B'),'ī');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'012C'),'&Ibreve;');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'012D'),'&ibreve;');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'012E'),'Į');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'012F'),'į');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0130'),'İ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0131'),'ı');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0132'),'IJ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0133'),'ij');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0134'),'Ĵ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0135'),'ĵ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0136'),'Ķ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0137'),'ķ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0139'),'Ĺ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'013A'),'ĺ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'013B'),'Ļ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'013C'),'ļ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'013D'),'Ľ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'013E'),'ľ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0141'),'Ł');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0142'),'ł');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0143'),'Ń');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0144'),'ń');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0145'),'Ņ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0146'),'ņ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0147'),'Ň');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0148'),'ň');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'014A'),'Ŋ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'014B'),'ŋ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'014C'),'Ō');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'014D'),'ō');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'014E'),'&Obreve;');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'014F'),'&obreve;');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0150'),'Ő');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0151'),'ő');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0152'),'Œ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0153'),'œ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0154'),'Ŕ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0155'),'ŕ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0156'),'Ŗ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0157'),'ŗ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0158'),'Ř');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0159'),'ř');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'015A'),'Ś');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'015B'),'ś');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'015C'),'Ŝ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'015D'),'ŝ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'015E'),'Ş');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'015F'),'ş');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0160'),'Š');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0161'),'š');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0162'),'Ţ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0163'),'ţ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0164'),'Ť');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0165'),'ť');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0166'),'Ŧ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0167'),'ŧ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0168'),'Ũ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0169'),'ũ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'016A'),'Ū');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'016B'),'ū');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'016C'),'Ŭ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'016D'),'ŭ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'016E'),'Ů');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'016F'),'ů');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0170'),'Ű');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0171'),'ű');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0172'),'Ų');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0173'),'ų');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0174'),'Ŵ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0175'),'ŵ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0176'),'Ŷ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0177'),'ŷ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0178'),'Ÿ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0179'),'Ź');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'017A'),'ź');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'017B'),'Ż');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'017C'),'ż');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'017D'),'Ž');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'017E'),'ž');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00D7'),'×');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0096'),'–');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0085'),'…');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'009A'),'š');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'008A'),'Š');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'2013'),'—');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'2014'),'–');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'2015'),'&mdash');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00E2'),'"');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00C3'),'"');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00C2'),'"');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0080'),'"');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0082'),'"');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0083'),'"');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0000'),'"');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'009C'),'"');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'009D'),'"');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0093'),'"');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0094'),'"');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00E2'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00C3'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00C2'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0080'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0082'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0083'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0000'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0099'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00BA'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00B9'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0098'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'2018'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'201A'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'2019'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'2039'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'203A'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0092'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, '—','—');

Find all non-ascii characters in MySQl column

SELECT *
  FROM products 
 WHERE DESCRIPTION <> CONVERT(DESCRIPTION USING ASCII)

search and replace unicode characters for ASCII output in perl

I don't know why you would EVER need this :P but just in case, this handles most of the open/close quote and double quote issues that arise when you need to output to ASCII

 $string =~ s/[\x{00E2}|\x{00C3}|\x{00C2}]?[\x{0080}|\x{0082}|\x{0083}|\x{0000}]?[\x{0099}|\x{00BA}|\x{00B9}|\x{0098}]/'/ig;
 $string =~ s/[\x{00E2}|\x{00C3}|\x{00C2}]?[\x{0080}|\x{0082}|\x{0083}|\x{0000}]?[\x{009C}|\x{009D}|\x{0093}|\x{0094}]/"/ig;
 $string =~ s/\x{2018}/'/ig;
 $string =~ s/\x{201A}/'/ig;
 $string =~ s/\x{2019}/'/ig;
 $string =~ s/\x{2039}/'/ig;
 $string =~ s/\x{203A}/'/ig; 
 $string =~ s/\x{0092}/'/ig; 
 
 $string =~ s/\x{0093}/"/ig;
 $string =~ s/\x{0094}/"/ig;
 $string =~ s/\x{201C}/"/ig;
 $string =~ s/\x{201D}/"/ig;
 $string =~ s/\x{201E}/"/ig;

Array: remove empty, remove whitespace out of each slice

For dealing with arrays with IDs (that have been touched by humans somewhere along the way)

@array = grep /\S/, @array; # remove empty
s/\s$// for @array; # remove whitespace out of each slot

Ember.js stateful image loading component

Not sure how useful this is, but this uses javascript Image() and a promise to load an image. Allows you to set loading and error states.

import Ember from 'ember';

const {
    Component,
    get,
    set
} = Ember;

export default Component.extend({

    isLoading: true,
    isError: false,

    /* JavaScript Image Object used to do the loading */
    imageLoader: Ember.computed(function() {
        return new Image();
    },

    getImage(url, _this) {
        return new Ember.RSVP.Promise((resolve, reject) => {
            let img = _this.get('imageLoader');
            img.onload = function() {
                resolve(url)
            }
            img.onerror = function() {
                reject(url)
            }
            img.src = url
        });
    },

    didInsertElement() {
        this._super(...arguments);
        let _this = this;

        let src = get(_this, 'src');
        _this.get('getImage')(src, _this).then(success => {
            console.log("success", success);
            set(_this, 'isLoading', false);
        }).catch(error => {
            console.log("fail", error);
            set(_this, 'isLoading', false);
            set(_this, 'isError', true);
        })
    },

    willDestroyElement() {
        this._super(...arguments);
        let img = get(this, 'imageLoader');
        if (img) {
            img = img.onload = img.onerror = null;
            this.set('imageLoader', null);
        }
    }
});

Then in the template you could do something like:

{{#if isLoading}}
 
loading...
{{else}}
 {{#if isError}}
  
 {{else}}
  
 {{/if}}
{{/if}}

If the component is called "product-render" you could use it in parent template like:

{{product-render src=myImage}}

MySQL select records with timestamp in the previous day, no matter what time of day it is now

WHERE timestamp >= DATE_SUB(CONCAT(CURDATE(), ' 00:00:00'), INTERVAL 24 HOUR) AND timestamp < DATE_ADD(DATE_SUB(CONCAT(CURDATE(), ' 00:00:00'), INTERVAL 24 HOUR), INTERVAL 24 HOUR)

Create Self-Signed Cert on OSX and tell the OS to trust it

Not as easy as you might think. Let's say you wanted to create a self-signed cert for a local domain called "my.webtool"... First create a file called v3.ext with these contents:

authorityKeyIdentifier=keyid,issuer
basicConstraints=CA:FALSE
keyUsage = digitalSignature, nonRepudiation, keyEncipherment, dataEncipherment
subjectAltName = @alt_names

[alt_names]
DNS.1 = my.webtool
DNS.2 = localhost
DNS.3 = 127.0.0.1

Then run these commands. This assumes you have openSSL installed:

openssl genrsa -des3 -passout pass:x -out my.webtool.pass.key 2048
openssl rsa -passin pass:x -in my.webtool.pass.key -out my.webtool.key
rm my.webtool.pass.key
openssl req -new -key my.webtool.key -out my.webtool.csr
openssl x509 -req -days 1000 -in my.webtool.csr -signkey my.webtool.key -out my.webtool.crt -extfile v3.ext

Then install the .key and .crt files in whatever server you're running. THEN you have to tell your system to trust the certificate by importing it into your keychain AND change the "trust" settings on it. See http://www.accuweaver.com/2014/09/19/make-chrome-accept-a-self-signed-certificate-on-osx/ update! this appears to be a one-shot deal:

openssl req \
-newkey rsa:2048 \
-x509 \
-nodes \
-keyout yarr.mydev.com.key \
-new \
-out yarr.mydev.com.crt \
-subj /CN=yarr.mydev.com \
-reqexts SAN \
-extensions SAN \
-config <(cat /etc/ssl/openssl.cnf \
    <(printf '[SAN]\nsubjectAltName=DNS:yarr.mydev.com,IP:192.168.56.101')) \
-sha256 \
-days 3650

clean the apache cache nicely

/usr/local/apache/bin/htcacheclean -vn -p/var/cache/apache -l500M

Kinda rough regex for pattern matching and capturing apache log info

the IP is parsed into \2 \3 and \4

^(((25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)) \- \- (\[.*?\]) "(.*?)" (\d+) (\d+) "(.*?)" "(.*?)"$

Geocode a batch of addresses with perl and Google

I am working with this store locator and needed a way to geocode 7k addresses, so I wrote this quick-n-dirty script. Google only lets you geocode 2500 addresses a day. The script will start where it left off, then quit when you hit the query limit. So it feasibly could be run on a cron until all your addresses are coded.

#!/usr/bin/perl

use strict;
use DBI;
use Google::GeoCoder::Smart;

my $geo = Google::GeoCoder::Smart->new();

my $host =""; 
my $database =""; 
my $user =""; 
my $mysqlpassword ="";

my $dbh = DBI->connect("DBI:mysql:database=$database;host=$host","$user","$mysqlpassword",{'RaiseError'=>1}); 

 my $ctr= 0;
 my $sql = "SELECT * FROM sb_locations WHERE lat = \"\"";  
 my $sth = &db_query($sql);
 while (my $ref = $sth->fetchrow_hashref) {
        my ($num, $error, @results, $returntext) = $geo->geocode(
        "address" => stripChars($ref->{'address'}), 
        "city" => stripChars($ref->{'city'}), 
        "state" => stripChars($ref->{'state'}), 
        "zip" => stripChars($ref->{'postal'})
        );
        my $lat;
        my $lng;
        eval {
         $lat = $results[0]{geometry}{location}{lat};
        };
        eval {
         $lng = $results[0]{geometry}{location}{lng};
        };  
        print "id: $ref->{'id'} returntext?: $returntext error?: $error lat: $lat lng: $lng\n";
        if ($error eq 'OVER_QUERY_LIMIT') {
         last;
        }         
        if (($lat) && ($lng)) {
         $lat = $dbh->quote($lat);
         $lng = $dbh->quote($lng);
         my $upd_q = "UPDATE sb_locations SET lat = $lat, lng = $lng WHERE id = $ref->{'id'}";
         my $sth2 = &db_query($upd_q);
         $sth2->finish();
        }
        $ctr++;
        if (($ctr%10)==0) {
         print "sleeping\n";
         sleep 5;
        }
 }
 $sth->finish();
 undef ($sql);
 undef ($sth);

$dbh->disconnect();

sub stripChars {
        my($text) = @_;
        $text =~ s/^\s*//; # strip out leading space if there is one
        $text =~ s/\n/ /g; # strip carraige returns
        $text =~ s/\t/ /g; # strip tabs
        $text =~ s/\a/ /g; # strip carraige returns
  $text =~ s/"/'/g; # strip quotes and replace with single quotes
        $text =~ s/\s+/ /g; # strip repeating spaces and replace with one
        $text =~ s/[^[:ascii:]]+//g;
        return ($text);
 
} # end sub strip chars

sub db_query {
 my ($query) = @_;
 my $sth = $dbh->prepare("$query"); 
 #print $query;
 $sth->execute;
 #print $dbh->err; 
 my $err = $dbh->err;  
 my $errstr = $dbh->errstr;
 if ($err) { print "$err: $errstr on query $query"; }
 return $sth;
 
} # end sub db_query