SQL Multi Column Search and Replace/Update Queries

I had coded something wrong.  I was searching and replacing a quote with / when inserting in to the database.  Problem I ran into is if you actually needed to use the / it replaced it with a quote.  After learning there are specific PHP libraries for escaping specific characters before inserting in to a database, I now needed to go back and correct my inserted data.  Now the amount of data I had to deal with was on the small side 200-300 rows, but either way to fix it by hand would take for ever.   Here is how I did it with SQL.

If you want to first search for a specific set of criteria:

select * from tablex where company like '%/%' or item like '%/%' or  description like '%/%';

How to search multiple columns:

update tablex set vendor = REPLACE(vendor, "/s", "'s"), item = REPLACE(item, "/s", "'s"), description = REPLACE(description, "/s", "'s")

How to search same column, but different search critera:

update tablex set description = REPLACE(description, "Men/s", "Men's"), description = REPLACE(description, "Woman/s", "Woman's")


Source:  Techonthenet

How to setup a remote connection to MySQL/Error: mysql said host is not allowed to connect to this mysql server

First connect to the server where you are able to currently connect (i.e.: remote desktop and connect using localhost)

Next run the following command from the MySQL command line:

mysql> GRANT ALL ON *.* TO 'jsmith'@'192.168.1.%' IDENTIFIED BY 'PASSWORD';

mysql> commit;

What this just did is created a user named jsmith that can connect from the network 192.168.1.x.  This works well if you use DHCP and every time you connect is a different IP.

Next thing I had to do was set the password for the user because I was getting an "Access Denied" error

Run the following command:

mysql> use mysql;

Database changed

mysql> update user set password=PASSWORD("new password") where USER='jsmith';

I then tried to connect and was able to connect at that point.

This is just a testing database, but I couldn't get the remote connection for root to work.  I understand where in almost all instances you wouldn't want that.  If anyone figures out how to do that, please post the solution in the comments.  I got past the not allowed to connect, but can't get past the access denied.

Source:  Link  Link  Link  Link