Oracle SQL add new column based on value of other column

Question

I'm looking for some help to create a new column based on values from another column - if this is even possible... This is not an ideal solution but I'm out running out of options.

I need to replace the beginning folder paths, change the direction of the \ and change the extension

Existing Field:

\\BRRNAKCL12\Audiofiles22\1Year\Diogarfngal_ZZZZZZZZZ\2020\Aug\03\5249013\5249013-07-25-18-96572.cca

New Field:

/location/TELEDATA/2020/Aug/03/5249013/5249013-07-25-18-96572.wav

Oracle version Version 19.2.1.247

Thank you in advance

Answer 1

You can add a new column to your table named NewField:

Alter table TableName add NewField varchar(500);

Then update NewField by replacing some characters as you wish from ExistingField.

update TableName set NewField= replace(replace(existingfield,'\','/'),'.cca','.wav')

Here I have just replace '' with '/' and '.cca' with '.wav'.

To replace path also:

update TableName set NewField= '/location/TELEDATA/'||substr(replace(replace(existingfield,'\','/'),'.cca','.wav'),instr(replace(replace(existingfield,'\','/'),'.cca','.wav'),'/2020',1,1) + 1)

DB-Fiddle:

Schema and insert statements:

 create table mytable (existingfield varchar(500));

 insert into mytable values('
 \\BRRNAKCL12\Audiofiles22\1Year\Diogarfngal_ZZZZZZZZZ\2020\Aug\03\5249013\5249013-07-25-18-96572.cca');

Add new column:

 Alter table mytable add NewField varchar(500);

Update query:

 update mytable set NewField= '/location/TELEDATA/'||substr(replace(replace(existingfield,'\','/'),'.cca','.wav'),instr(replace(replace(existingfield,'\','/'),'.cca','.wav'),'/2020',1,1) + 1)

Select query:

 select * from mytable;

Output:

EXISTINGFIELD	NEWFIELD
\BRRNAKCL12\Audiofiles22\1Year\Diogarfngal_ZZZZZZZZZ\2020\Aug\03\5249013\5249013-07-25-18-96572.cca	/location/TELEDATA/2020/Aug/03/5249013/5249013-07-25-18-96572.wav

db<>fiddle here