oracle-databaseregexp-like

Updating a data in Oracle dB


I have a datas in my Oracle DB that look like these : 2015-2265-0201 (dddd-dddd-dddd) in a column called "cote".

However, through a bad manipulation, some of these look like these : 201522650201

or these: 2015-2265-02-01

I want to change them so i ran these command :

UPDATE CSL.STOCKAGES
    SET
        COTE = REGEXP_REPLACE(COTE, '(\d\d\d\d)(\d\d\d\d)(\d\d\d\d)' , '\1-\2-\3')
    WHERE
    REGEXP_LIKE(COTE, '\d\d\d\d\d\d\d\d\d\d\d\d');
UPDATE CSL.STOCKAGES
SET
    COTE = REGEXP_REPLACE(COTE, '(\d\d\d\d-\d\d\d\d-\d\d)-(\d\d)' , '\1\2')
WHERE
    REGEXP_LIKE(COTE, '\d\d\d\d-\d\d\d\d-\d\d-\d\d');

However it doesn't work. I tried to see what the datas look like and when i run :

SELECT * FROM CSL.STOCKAGES WHERE
    REGEXP_LIKE(COTE, '\d\d\d\d-\d\d\d\d-\d\d\d\d');

It shows my datas that are correct but when I run :

SELECT * FROM CSL.STOCKAGES WHERE
    REGEXP_LIKE(COTE, '\d\d\d\d-\d\d\d\d-\d\d-\d\d');

or

SELECT * FROM CSL.STOCKAGES WHERE
    REGEXP_LIKE(COTE, '\d\d\d\d\d\d\d\d\d\d\d\d');

It doesn't show me anything. What am I doing wrong ? Why the datas don't show ?

Here you can see a sample of the datas : sample of data


Solution

  • You can combine all of your regular expressions into a single statement:

    UPDATE CSL.STOCKAGES
    SET COTE = REGEXP_REPLACE(
                 COTE,
                 '(\d{4})-?(\d{4})-?(\d{2})-?(\d{2})',
                 '\1-\2-\3\4'
               )
    WHERE REGEXP_LIKE(
            COTE,
            '(\d{4})-?(\d{4})-?(\d{2})-?(\d{2})'
          )
    AND   NOT REGEXP_LIKE(
            COTE,
            '\d{4}-\d{4}-\d{4}'
          );
    

    Which, for the sample data:

    CREATE TABLE stockages (cote) AS
    SELECT '200209650304' FROM DUAL UNION ALL
    SELECT '200209650301' FROM DUAL UNION ALL
    SELECT '200209650302' FROM DUAL UNION ALL
    SELECT '2015-2265-02-04' FROM DUAL UNION ALL
    SELECT '2015-2265-02-04' FROM DUAL UNION ALL
    SELECT '2015-2265-0203' FROM DUAL UNION ALL
    SELECT '2015-2265-0202' FROM DUAL;
    

    After the update the table contains:

    COTE
    2002-0965-0304
    2002-0965-0301
    2002-0965-0302
    2015-2265-0204
    2015-2265-0204
    2015-2265-0203
    2015-2265-0202

    If you are finding that it does not update some rows then you need to check the values of those rows and see if there are some values that do not match your regular expression:

    SELECT COTE, DUMP(cote)
    FROM   CSL.STOCKAGES
    WHERE  NOT REGEXP_LIKE(
             COTE,
             '(\d{4})-?(\d{4})-?(\d{2})-?(\d{2})'
           );
    

    db<>fiddle here