Trimming text/varchar column when duplicates not allowed

Question

So i have a mysql table, like the one above where the column Product

ID	Product_Name
1	text1
2	text1 \n
3	text2
4	text3
5	text4 \n

My problem is that i'm not very smart - and have introduced ALOT of products to my table with an new-line in the end. With the following query i figured out, that i had 200k+ rows with a new line:

SELECT * FROM `my_product_table` WHERE `Product_Name` REGEXP "\n"

I'm looking for a way to trim - all "Product_Name" with a newline if another product with that exact name does not exist already - and if it exits already, i just want to remove it.

I figured the easyiest way was to:

Trim all rows possible - since it will throw an error if a duplicate exists, so nothing will happen
After everything possible is trimmed - delete the rest

Step 2. is should be easy, by just modifying my query above - but my MYSQL knowledge is sadly lacking a little, when i'm trying step 1. Is this the way to do it? and is it even possible?

EDIT I think i was not clear about the new line, i dont know how to write it in tables on stack, but see this image https://i.stack.imgur.com/kG5oD.jpg - thats how the duplicate value with the "new line" looks

Is there a unique index on product name? – P.Salmon Mar 29 '23 at 13:21 — P.Salmon, Mar 29 '23 at 13:21
Yes, the product_name column have a unique index – Mac Mar 29 '23 at 13:23 — Mac, Mar 29 '23 at 13:23

nbk · Answer 1 · 2023-03-29T13:29:11.377

You can first eliminate the duplicates and then UPDATE the rest

CREATE TABLE Table1
    (`ID` int, `Product_Name` varchar(8))
;
    
INSERT INTO Table1
    (`ID`, `Product_Name`)
VALUES
    (1, 'text1'),
    (2, 'text1 \n'),
    (3, 'text2'),
    (4, 'text3'),
    (5, 'text4 \n')
;

Records: 5  Duplicates: 0  Warnings: 0

DELETE FROM Table1
WHERE `Product_Name` LIKE '%\n'
AND EXISTS ( SELECT 1 FROM (SELECT * FROM Table1) t2 
  WHERE t2.`Product_Name` = TRIM(REPLACE(Table1.`Product_Name`,'\n','')));

UPDATE Table1 SET `Product_Name` = TRIM(REPLACE(`Product_Name`,'\n','')) WHERE `Product_Name` LIKE '%\n'

Rows matched: 1  Changed: 1  Warnings: 0

SELECT * FROM Table1

ID	Product_Name
1	text1
3	text2
4	text3
5	text4

fiddle

In case you have also cr lf or cr alone

CREATE TABLE Table1
    (`ID` int, `Product_Name` varchar(8))
;
    
INSERT INTO Table1
    (`ID`, `Product_Name`)
VALUES
    (1, 'text1'),
    (2, 'text1 \n'),
    (3, 'text2'),
    (4, 'text3'),
    (5, 'text4 \n'),
  (6, 'text1 \r'),
  (7, 'text1 \r\n')
;

Records: 7  Duplicates: 0  Warnings: 0

DELETE FROM Table1
WHERE `Product_Name` LIKE '%\n' OR `Product_Name` LIKE '%\r' OR `Product_Name` LIKE '%\r\n'
AND EXISTS ( SELECT 1 FROM (SELECT * FROM Table1) t2 
  WHERE t2.`Product_Name` = TRIM(REPLACE(REPLACE(REPLACE(Table1.`Product_Name`,'\r\n',''),'\r',''),'\n','')));

UPDATE Table1 SET `Product_Name` = TRIM(REPLACE(REPLACE(REPLACE(`Product_Name`,'\r\n',''),'\r',''),'\n','')) 
  WHERE `Product_Name` LIKE '%\n' OR `Product_Name` LIKE '%\r' OR `Product_Name` LIKE '%\r\n'

Rows matched: 0  Changed: 0  Warnings: 0

SELECT * FROM Table1

ID	Product_Name
1	text1
3	text2
4	text3

fiddle

i tried your solution but i can not get it to work - i think its because i did not communicate clearly - its not a \n (its an actual new line): See this: https://imgur.com/a/1d4WI5z — Mac, Mar 29 '23 at 13:14
images do't help. at the text from ypur database to the sample fiddle, may you don't have \n alone see https://stackoverflow.com/a/1552782/5193536 for all possibility and try them also — nbk, Mar 29 '23 at 13:20

Trimming text/varchar column when duplicates not allowed

1 Answers1