Remove duplicated number in column

Struggling with duplicate numbers in a database column? Learn how to clean your data with a simple Oracle PL/SQL function.

Problem

Last time I have a problem with duplicated number in post address (column). Our callback interfaces write to us many error_number. Bad formatted duplicated numbers. So first I have to find where is and how many this number are. How remove duplicated in field number?

Bad numbers:

CREATE TABLE locations
(
   site_number     INT,
   ADR_STREET_NO   VARCHAR (7)
);

INSERT INTO locations (site_number, ADR_STREET_NO) VALUES (2, '22 22');
INSERT INTO locations (site_number, ADR_STREET_NO) VALUES (1, '1 111');
INSERT INTO locations (site_number, ADR_STREET_NO) VALUES (3, '3 3');
INSERT INTO locations (site_number, ADR_STREET_NO) VALUES (3, '43 645');
INSERT INTO locations (site_number, ADR_STREET_NO) VALUES (3, '72 85');
INSERT INTO locations (site_number, ADR_STREET_NO) VALUES (3, '23 95');
INSERT INTO locations (site_number, ADR_STREET_NO) VALUES (3, '711 711');

SITE_NUMBER	ADR_STREET_NO
2	22 22
1	1 111
3	3 3
3	43 645
3	72 85
3	23 95
3	711 711

How to solve the problem?

SELECT l.site_number, l.ADR_STREET_NO, REGEXP_REPLACE (l.ADR_STREET_NO, '([^ ]+)( \1)+', '\1')
FROM mibp_owner.locations l
WHERE REGEXP_LIKE (ADR_STREET_NO, '\d \d'); 
--
UPDATE mibp_owner.locations l
SET l.ADR_STREET_NO = REGEXP_REPLACE (l.ADR_STREET_NO, '([^ ]+)( \1)+', '\1')
WHERE REGEXP_LIKE (ADR_STREET_NO, '\d \d');

ITE_NUMBER	ADR_STREET_NO	REGEXP_REPLACE(L.ADR_STREET_NO,'([^]+)(\1)+’,’\1′)
2	22 22	22
1	1 111	111
3	3 3	3
3	43 645	43 645
3	72 85	72 85
3	23 95	23 95
3	711 711	711

After update:

SELECT l.site_number, l.ADR_STREET_NO, REGEXP_REPLACE (l.ADR_STREET_NO, '([^ ]+)( \1)+', '\1')
  FROM locations l
 WHERE REGEXP_LIKE (ADR_STREET_NO, '\d \d');

SITE_NUMBER	ADR_STREET_NO	REGEXP_REPLACE(L.ADR_STREET_NO,'([^]+)(\1)+’,’\1′)
3	43 645	43 645
3	72 85	72 85
3	23 95	23 95

This example online: http://sqlfiddle.com/#!4/b84dbf/1.

RegEx is explained in SQL – Search for a particular string in Oracle CLOB column

Function to remove double number:

CREATE OR REPLACE FUNCTION rem_dubble (n in VARCHAR2)
   RETURN VARCHAR2
IS
  ind integer;
  t1 varchar2(100);
  t2 varchar2(100);
BEGIN
  if is_no_dubbled(n)='N' then
    return n;
  end if;
  for i in 1..10 loop
    ind:=instr(n,' ',1,i);
    if ind=0 then
      exit;
    end if;
    t1:=trim(substr(n,1,ind));
    t2:=trim(substr(n,ind));
    if t1=t2 then
      return rem_dubble(t1);
    end if;
  end loop;
  return n;
END;
/

Create function to find duplicate and sign it:

CREATE OR REPLACE FUNCTION is_no_dubbled (n in VARCHAR2)
   RETURN VARCHAR2
IS
  ind integer;
  t1 varchar2(100);
  t2 varchar2(100);
BEGIN
  if instr(n,' ')=0 then return 'N'; end if;
  for i in 1..10 loop
    ind:=instr(n,' ',1,i);
    if ind=0 then
      exit;
    end if;
    t1:=trim(substr(n,1,ind));
    t2:=trim(substr(n,ind));
    if t1=t2 then
      return 'Y';
    end if;
  end loop;
  return 'N';
END;