Sqlite3 string format (interpolation)

Question

I have a table with 2 text columns that I need to interpolate.

Row1

Col1 = Trees.#.Bud.Flower.#.Sepal

Col2 = ['1','0']

Row2

Col1 = Trees.#.Bud.Flower.#.Sepal.#.Colour

Col2 = ['1','0','0']

Desired results =

Row1 = Trees.1.Bud.Flower.0.Sepal

Row2 = Trees.1.Bud.Flower.0.Sepal.0.Colour

The closest I've gotten thus far is this...

SELECT 

    FORMAT(Node, '1','2','','','','','') 

    FROM ( 

        SELECT 

         'Trees.%s.Bud.Flower.%s.Sepal' as Node 

         )

But stumped at how to utilize my "Col2" to provide the multiple individual arguments required for the FORMAT function.

Very much hoping that there is a solution without having to process the datasets line-by-line, or getting into UDFs which I understand aren't simple in sqlite 😞

Any help appreciated 🤞

Answer 1

If you have a maximum known replacement to do, you can pass unused value in FORMAT:

SELECT
 T.column1,
 T.column2,
 FORMAT(
   REPLACE(T.column1, '#', '%s'),
   json_extract(T.column2, '$[0]'),
   json_extract(T.column2, '$[1]'),
   json_extract(T.column2, '$[2]'),
   json_extract(T.column2, '$[3]'), -- unused
   json_extract(T.column2, '$[4]'), -- unused
   json_extract(T.column2, '$[5]'), -- unused
   json_extract(T.column2, '$[6]'), -- unused
   json_extract(T.column2, '$[7]')  -- unused
 ) As Formated
FROM (
  VALUES
    ('Trees.#.Bud.Flower.#.Sepal', '[''1'',''0'']'),
    ('Trees.#.Bud.Flower.#.Sepal.#.Colour', '[''1'',''0'',''0'']')
) T;