Updating a data in Oracle dB-CodePudding

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');

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

CodePudding user response：

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

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