# Remove duplicated number in column

- Link: https://ultimasolution.pl/sql-corner-remove-duplicated-number/
- Published: 2026-04-10T09:06:10+02:00
- Author: Karol Preiskorn

# 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:

    ```EnlighterJSRAW
    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?

    ```EnlighterJSRAW
    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:

    ```EnlighterJSRAW
    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](http://sqlfiddle.com/#!4/b84dbf/1).

RegEx is explained in [SQL – Search for a particular string in Oracle CLOB column](https://www.ultimasolution.pl/sql-search-for-a-particular-string-in-oracle-clob-column/)

Function to remove double number:

    ```EnlighterJSRAW
    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:

    ```EnlighterJSRAW
    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;
    ```