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