SQL: Reverse Position of String

Question

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

sort the data - find out the first occurrence - delete the rest — neurotic-d
– neurotic-d, Commented Jun 30, 2016 at 11:09
Edit your question and provide additional information: (1) what is the database you are using; (2) what results do you want; (3) are your values in one column or two? (4) what have you tried? — Gordon Linoff
– Gordon Linoff, Commented Jun 30, 2016 at 11:09
You can find the enries you want to delete by SQL query with table test and table column nam like select * from test where nam > reverse(nam) and nam in (select reverse(nam) from test). But I do not know how to delete them, since it is not allowed to reference the table you want to delete in a where-clause. Maybe you can save them and delete with the next step. — irene
– irene, Commented Jun 30, 2016 at 11:47

Jocelyn · Accepted Answer · 2016-06-30 12:17:19Z

1

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.

answered Jun 30, 2016 at 12:17

Jocelyn

11.4k11 gold badges47 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Prosenjit · Accepted Answer · 2016-06-30 20:51:30Z

0

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

answered Jun 30, 2016 at 20:51

Prosenjit

13 bronze badges

Collectives™ on Stack Overflow

SQL: Reverse Position of String

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related