Sanitize During MySQL Insert

Question

I'm currently doing a MySQL insert like this:

INSERT INTO RESI (MarketingRemarks) 
SELECT PropertyInformation FROM `property_res`;

I then run independant commands to clean the RESI table of any strange characters like this:

UPDATE RESI SET MarketingRemarks = REPLACE(MarketingRemarks, '“', '"');
UPDATE RESI SET MarketingRemarks = REPLACE(MarketingRemarks, '”', '"');
UPDATE RESI SET MarketingRemarks = REPLACE(MarketingRemarks, '–', '-');
UPDATE RESI SET MarketingRemarks = REPLACE(MarketingRemarks, '½', '1/2');
UPDATE RESI SET MarketingRemarks = REPLACE(MarketingRemarks, '’', '\'');

This seems very inefficient to have all these queries. Is there a way to combine this into a single INSERT statement?

I've tried numerous ways to place the REPLACE into the INSERT statement but with no luck.

answered before. https://stackoverflow.com/questions/1671040/can-mysql-replace-multiple-characters — Arquillian, Sep 12 '18 at 14:55
@Arquillian I see no INSERT statement here. That is not a valid answer. — Rocco The Taco, Sep 12 '18 at 14:56

score 1 · Accepted Answer · answered Sep 12 '18 at 15:00

You may use replace function in your select clause, like this:

INSERT INTO RESI (MarketingRemarks) 
SELECT REPLACE(REPLACE(PropertyInformation, '½', '1/2'), '“', '"') FROM `property_res`;

and you need to build chain of your replace function like in example provided by @Arquillian

score 0 · Answer 2 · answered Sep 12 '18 at 15:06

I would suggest to create a function that sanitize the string, and use the function from the insert into command. You'll have an easy readable code.

DELIMITER $$
USE `nestor_dbo`$$
CREATE FUNCTION `Sanitize` (src NVARCHAR(100))
RETURNS NVARCHAR(100)
BEGIN
    DECLARE result NVARCHAR(100);

    SET result = src;

    SET result = replace(result, '“', '"');
    SET result = replace(result, '”', '"');
    SET result = replace(result, '–', '-');
    SET result = replace(result, '½', '1/2');
    SET result = replace(result, '’', '\'');

RETURN result;
END$$


INSERT INTO RESI (MarketingRemarks) 
SELECT Sanitize(PropertyInformation) FROM `property_res`;

Sanitize During MySQL Insert

2 Answers2