Abimbola Folorunso Abimbola Folorunso - 8 months ago 34
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?


For adjacent words

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


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