Abimbola Folorunso Abimbola Folorunso - 28 days ago 5
SQL Question

Detect duplicate string or word in a row

I want to know how to detect duplicate word in a row. This is to ensure that we have clean data in our database.

For example see below

Name count
James James McCarthy 1
Donald Hughes Hughes 1


I want the result to be like

Name count
James McCarthy 1
Donald Hughes 1


Is there a solution to this using Oracle SQL?

Answer

For adjacent words

select  1
from    dual
where   regexp_like ('John John Doe','(^|\s)(\S+)\s+\2(\s|$)')
;

or

select  case when regexp_like ('John John Doe','(^|\s)(\S+)\s+\2(\s|$)') then 'Y' end as adj_duplicate
from    dual
;