Weblog entry #152 for simonw
See all weblog entries made by simonw.
#152
MySQL string function "replace" example
Posted by simonw on Wed 7 Mar 2007 at 13:19
We got some UTF-8 long dashes in a database where we didn't want them.
update products set ProdShortDesc=replace(ProdShortDesc,'--','-') where prodShortDesc like '%--%';
These days MYSQL has a replace utility, a "replace" command (replaces "insert", doing an "insert" or a "delete and insert", depending whether a record matching the primary key, or a unique index, is matched. Which, because it does the delete (and cascading deletes) is a lot less useful than "insert .. on duplicate" syntax. Both of which make finding the syntax, if you are low on brain power, harder to find.
Of course to cut and paste the longer dash, I had to set the locale to a UTF-8 locale. I notices that the MySQL website makes the same mistake. Anyway the web pages on which the data are displayed will soon be set to "utf-8" instead of "iso-8859" and hopefully then they can put all sorts of grubby characters in the database and have them displayed as they wish.
Of course this posting then messes up the character - trust me - there was a single long dash there at some point, but I used "--" to avoid complications.
update products set ProdShortDesc=replace(ProdShortDesc,'--','-') where prodShortDesc like '%--%';
These days MYSQL has a replace utility, a "replace" command (replaces "insert", doing an "insert" or a "delete and insert", depending whether a record matching the primary key, or a unique index, is matched. Which, because it does the delete (and cascading deletes) is a lot less useful than "insert .. on duplicate" syntax. Both of which make finding the syntax, if you are low on brain power, harder to find.
Of course to cut and paste the longer dash, I had to set the locale to a UTF-8 locale. I notices that the MySQL website makes the same mistake. Anyway the web pages on which the data are displayed will soon be set to "utf-8" instead of "iso-8859" and hopefully then they can put all sorts of grubby characters in the database and have them displayed as they wish.
Of course this posting then messes up the character - trust me - there was a single long dash there at some point, but I used "--" to avoid complications.