MySQL - Replace Character in Columns

Question

Being a self-taught newbie  I created a large problem for myself  Before inserting data in to my database  I ve been converting apostrophes     in a string  to double quotes       instead of the required back-slash and apostrophe       which MySQL actually requires   Before my table grows more than the 200 000 rows it already is  I thought it was best to rectify this issue immediately  So I did some research and found the SQL REPLACE function  which is great  but I m now confused   In ASP  I was doing this   str   Replace str             If I look at my database in SQL Workbench  the symbol I converted is now a single quote      which has confused me a little  I understand why it changed from double to single  but I don t know which one I m meant to be changing now    To go through and rectify my problem using SQL REPLACE  do I now convert single quotes     to back-slash and apostrophes      or do I convert double quotes      to back-slash and apostrophes        For example  this   SQL     SELECT REPLACE myColumn            FROM myTable     or this   SQL     SELECT REPLACE myColumn           FROM myTable     I hope I explained myself well  any suggestions gratefully received as always  Any queries about my question  please comment   Many thanks  -- UPDATE --  I have tried the following queries but still fail to change the       in the data   SELECT REPLACE caption            FROM photos WHERE photoID   3371 SELECT REPLACE caption           FROM photos WHERE photoID   3371 SELECT REPLACE caption            FROM photos WHERE photoID   3371   Yet if I search   SELECT COUNT    FROM photos WHERE caption LIKE         I get 16 150 rows   -- UPDATE 2 --  Well  I have created a  workaround   I managed to convert an entire column pretty quickly writing an ASP script  using this SQL   SELECT photoID  caption FROM photos WHERE caption LIKE           and then in ASP I did   caption   Replace caption              But I would still like to know why I couldn t achieve that with SQL

User · Answer

maybe I d go by this    SQL   SELECT REPLACE myColumn              FROM myTable   I used singlequotes because that s the one that registers string expressions in MySQL  or so I believe   Hope that helps

User · Answer

Replace below characters                  amp              -                           lt   gt           with this SQL  SELECT note as note original        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                                                                                      REPLACE                                                                                          REPLACE                                                                                              REPLACE note                                                                                                                                                                                                                                                                                                                                                                                     lt                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            amp                                                                                                                                                                                                                                                                                                                                -                      gt                       -         --    -   as note changed FROM invheader

User · Answer

Just running the SELECT statement will have no effect on the data   You have to use an UPDATE statement with the REPLACE to make the change occur   UPDATE photos    SET caption   REPLACE caption             Here is a working sample  http   sqlize com 7FjtEyeLAh

User · Answer

If you have  something  and need  something   use replace col              and viceversa

[mysql] MySQL - Replace Character in Columns

Examples related to mysql

Examples related to sql

Examples related to database