CODEWITHSUNDEEP
mysqlsql
Web Buddy
Web Buddy

Reputation: 9

SQL: Reverse Position of String

I have a table having below data,trying to delete duplicate rows from table. For example:ab=ba,cd=dc..

Please suggest.

ab
ac
ad
ba
bc
bd
da
db
dc
ea
eb
ec
ed
fa
fb
fc
fd

Upvotes: 0

Views: 257

Answers (2)

Prosenjit
Prosenjit

Reputation: 1

Delete from test22 a where (select count(1) from test22 b where id =a.id or id=reverse(a.id)) >1 and rowid <> (select min ( rowid) from test22 c where c.id=a.id or c.id =reverse (a.id ))

Suppose test22 is the table and column contains duplicate values is id

Upvotes: 0

Jocelyn
Jocelyn

Reputation: 11393

This query joins the table with itself. The WHERE condition ensures t2 only holds the values to delete (ie. it will delete ca but not ac).

DELETE t2.*
FROM `table` t1
INNER JOIN `table` t2 ON t1.letters<t2.letters AND REVERSE(t1.letters)=t2.letters;

If you want to see what values would be deleted, just replace DELETE with SELECT in the query.

Upvotes: 1

Related Questions