CODEWITHSUNDEEP
mysql
Arpit Gupta
Arpit Gupta

Reputation: 1277

MySQL Query for Similar Column Value

In my database table, Due to some mistake same row has been entered twice, But it is now having one column value with some appended numeric value in the duplicate entry.

For example, If my table has a column named filename, Then in one row it has value 'some-random-name.pdf'.

And in the duplicate row, it has value 'some-random-name-1532.pdf'.

I need to identify all such records. Please note that there can be any or zero number of dash(-) in the filename. So Like query something like '%-____-%.pdf did not help me.

Upvotes: 0

Views: 30

Answers (1)

Akina
Akina

Reputation: 42632

Assuming that complete filename contains only one dot between name and extension you may try this:

WHERE SUBSTRING_INDEX(t1.value, '.', -1) = SUBSTRING_INDEX(t2.value, '.', -1)
  AND LOCATE(SUBSTRING_INDEX(t1.value, '.', 1), SUBSTRING_INDEX(t2.value, '.', 1)) = 1

Upvotes: 1

Related Questions