CODEWITHSUNDEEP
mysql
Haim
Haim

Reputation: 1167

Mysql select where same value twice in same field

I have a cell that has
'200 Example Avenue 209 Example Avenue' in it.

I'm not sure how the line break gets in there. I would like to run a query on the table that says: IF the content till the line break matches the text after the linebreak, then delete the text after the line break.

I'm not sure where to start - any pointers would be much appreciated.

Upvotes: 0

Views: 749

Answers (2)

Bruno Degomme
Bruno Degomme

Reputation: 1143

You can use REGEXP_SUBSTR to extract the cell value up to and after the newline respectively, and compare both values. For example

UPDATE tablename
SET columname = TRIM(TRAILING '\n' FROM REGEXP_SUBSTR(columname,".*\n")) 
WHERE TRIM(TRAILING '\n' FROM REGEXP_SUBSTR(columname,".*\n")) = 
           TRIM(LEADING '\n' FROM REGEXP_SUBSTR(columname,"\n.*"))                                 
;

For example : https://www.db-fiddle.com/f/81juEza4M9QAtRpdLagWhh/0#&togetherjs=QbvSuquqwk

Upvotes: 0

forpas
forpas

Reputation: 164099

Compare the 2 substrings before and after char(10) and if they are equal keep only the 1st substring:

update tablename
set value = left(value, instr(value, char(10)) - 1)
where 
  value like concat('_%', char(10), '%_')        
  and                               
  left(value, instr(value, char(10)) - 1) = right(value, length(value) - instr(value, char(10)))

Change value to your column's name.
See the demo.

Upvotes: 1

Related Questions