Monday, September 9, 2013

Oracle PL/SQL search for repeated substring

Short Description:  Query to find instances of a substring immediately repeated in a single string.

Description:  the query below finds a substring repeated immediately after the first.  My current example is searching username data for records for which the first and last name are the same.

SELECT USERNAME FROM SCHEMA.TABLE
where
trim(SUBSTR(USERNAME, INSTR(USERNAME, ' ', 1, 1)+1))= trim(substr(USERNAME, 1, instr(USERNAME,' ')))

No comments:

Post a Comment