Replacing select characters

Question

I have a list of fields with me obtained from parsing nested JSON data. Following is an example schema. The no of fields is dynamic in nature

yrseqtax_lots_0_lot_pricetax_lots_0_lot_seq_notax_lots_1_lot_pricetax_lots_0_lot_seq_no201902101244645

I need to convert all the fields of type tax_lots_0_lot_price to tax_lots.0.lot_price. Basically replace the "_0_" to something like ".0.". I have transposed the table to make the columns as field value. Tried applying a REGEX_REPLACE method with a regex string of "_\d+_" The issue is when I want to replace the string I want it to retain the numeric value too.

Your suggestions are most welcome. Thank you for your time

Luke_C · Accepted Answer

@fardeen9983

Here's a more scalable approach, since you have JSON data I assume there will ultimately be a large number of columns that are 0,1,2, and so on.

Similar to @messi007, I use the dynamic rename but first rename the fields in a way that any number value is maintained.