CODEWITHSUNDEEP
mysqlsqlsql-update
Jonathan Grant
Jonathan Grant

Reputation: 143

Removing carriage returns from database

I am so confused. I used the following query to get rid of unwanted returns in the data. It worked fine for all the fields except "first_name". Instead it changed all the first_name fields to "0". There is no difference between the first_name replace and the rest, right? I don't understand what happened!

UPDATE donors
    SET 
        `first_name` = Replace(Replace(`first_name`, CHAR(13), ''), CHAR(10), '')
    AND
        `last_name` = Replace(Replace(`last_name`, CHAR(13), ''), CHAR(10), '')
    AND
        `address_1` = Replace(Replace(`address_1`, CHAR(13), ''), CHAR(10), '')
    AND
        `city` = Replace(Replace(`city`, CHAR(13), ''), CHAR(10), '')
    AND
        `zipcode` = Replace(Replace(`zipcode`, CHAR(13), ''), CHAR(10), '')

I am using MYSQL, and this is the only query I used. The change happened when I executed it. I copy and pasted it here.

Here is the DDL:

CREATE TABLE IF NOT EXISTS `donors` (
  `donor_id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` text NOT NULL,
  `last_name` text NOT NULL,
  `company` text NOT NULL,
  `donor_notes` text NOT NULL,
  `email_1` text NOT NULL,
  `email_2` text NOT NULL,
  `phone_1` text NOT NULL,
  `phone_2` text NOT NULL,
  `fax` text NOT NULL,
  `address_1` text NOT NULL,
  `address_2` text NOT NULL,
  `city` text NOT NULL,
  `state` varchar(50) NOT NULL,
  `zipcode` varchar(11) NOT NULL,
  `country` text NOT NULL,
  `password` text NOT NULL,
  `salt` text NOT NULL,
  `newsletter` text NOT NULL,
  `address_id` text NOT NULL,
  `ip` text NOT NULL,
  `status` text NOT NULL,
  `approved` text NOT NULL,
  `date_created` datetime NOT NULL,
  PRIMARY KEY (`donor_id`)
) ENGINE=InnoDB AUTO_INCREMENT=445 DEFAULT CHARSET=latin1;

Upvotes: 0

Views: 59

Answers (1)

Andrew
Andrew

Reputation: 7880

As I mentioned in my comment, I think the problem are all those AND. In MySql, between each updated field you should insert a comma, not an AND, which is used for conditions.

So when you ran this:

UPDATE donors
SET 
    `first_name` = Replace(Replace(`first_name`, CHAR(13), ''), CHAR(10), '')
AND
    `last_name` = Replace(Replace(`last_name`, CHAR(13), ''), CHAR(10), '')
....

What you actually did was:

UPDATE donors
SET 
    `first_name` = some_varchar AND condition AND condition AND condition AND condition

Each one of these conditions was comparing each column to itself after doing the replacements. Just being one of those false, the result was 0. And that's why that value was stored in your column.

Your script should have been like this:

UPDATE donors
SET 
    `first_name` = Replace(Replace(`first_name`, CHAR(13), ''), CHAR(10), ''),
    `last_name` = Replace(Replace(`last_name`, CHAR(13), ''), CHAR(10), ''),
    `address_1` = Replace(Replace(`address_1`, CHAR(13), ''), CHAR(10), ''),
    `city` = Replace(Replace(`city`, CHAR(13), ''), CHAR(10), ''),
    `zipcode` = Replace(Replace(`zipcode`, CHAR(13), ''), CHAR(10), '')

References:

https://www.mysqltutorial.org/mysql-update-data.aspx

https://dev.mysql.com/doc/refman/8.0/en/update.html

By the way, all those ` are not necessary, you only need them when the column has spaces or is a reserved keyword.

Upvotes: 1

Related Questions