Update whole Column values in a table in oracle sql to a different format where different

Consider Table barcode has a column named b_values has values stored in two different formats. One with dashes and other without.

e.g., XXXXXXXXXXXXXXX -> XXX-XXXX-XXXXXXX-X

Length without dashes is 15 and with dashes 18.

I tried following but it did not work.

UPDATE barcode b
   SET b.barcode = CASE
                     WHEN b.barcode LIKE '%-%-%-%' THEN
                      b.barcode
                     WHEN b.barcode NOT LIKE '%-%-%-%' AND LENGTH(b.barcode) = 15 THEN
                      SUBSTR(b.barcode, 0, 3).CONCAT('-').CONCAT(SUBSTR(b.barcode, 4, 4)).CONCAT('-').
                      CONCAT(SUBSTR(b.barcode, 7, 7)).CONCAT('-').CONCAT(SUBSTR(b.barcode, 14, 1))
                     ELSE
                      b.barcode
                   END

I am trying to work on a script which will find all values without dashes in same column and update them including dashes.

I don’t need the values in a result from query. I’m trying to update the values stored in table.

>Solution :

You can do it as follows :

UPDATE mytable
SET barcode = SUBSTR(barcode, 1, 3) || '-' || SUBSTR(barcode, 4, 4) || '-' || SUBSTR(barcode, 8, 7) || '-' ||  SUBSTR(barcode, 15, 1)               
WHERE barcode NOT LIKE '%-%-%-%' AND LENGTH(barcode) = 15

Demo here