Find & Replace the values in curly braces with value from other tables

Question

I have trouble in writing a SQL query.

I have a data table with below columns.

DataTable 1

      id        control_ id     notes
           ----------------------------------------------------------------
        1     ac-1      The Organization
       1.1   ac-1      document disseminate to {<!-- -->{param = "ac-1_prm_1"}} and {<!-- -->{param = "ac-1_prm_2"}}
       2      ac-2     develop document  to {<!-- -->{param = "ac-1_prm_3"}}
      2.1    ac-2      Test

Data table Parameter

 parameterid     value.                 control_id
 -------------------------------—————----------------
 ac-1_prm_1      apple doc.              ac-1
 ac-1_prm_2      google doc.           ac-1
 ac-1_prm_3      facebook doc.        ac-2

I need create a final_notes column:

 control_id     notes                                                       final_notes 
 ---------------------------------------------------------------------------------------------     
  ac-1        The Organization                                  The Organization
  ac-1       develop document disseminate            develop document disseminate to apple 
                to{<!-- -->{param = "ac-1_prm_1"}}      doc and google doc
               and {<!-- -->{param = "ac-1_prm_2"}}        


  ac-2     develop document                                  develop document facebook doc 
              to {<!-- -->{param = "ac-1_prm_3"}}
  ac-2    Test                                                           Test

Answer 1

Hi， @Aditi Sharma

Welcome to the microsoft tsql Q&A forum!

Please also check:

WITH cteA AS  
(  
SELECT P1.control_id,(SELECT VALUE+' and ' FROM DataTableParameter P2  
                          WHERE P1.control_id = P2.control_id  
                        FOR XML PATH ('')) AS 'Val'  
FROM DataTableParameter P1  
),  
cteB AS  
(  
SELECT control_id,SUBSTRING (Val,1, LEN(Val)-5 ) AS VALUE  
FROM cteA  
)  
  
SELECT DISTINCT D.control_id,notes,  
    CASE   
      WHEN CHARINDEX('{',notes) > 0  
      THEN REPLACE (notes,  
                     SUBSTRING (notes,  
                               CHARINDEX('{',notes),  
                              LEN(notes)-CHARINDEX('{',notes)+1),   
                    VALUE)   
      ELSE notes  
    END AS final_notes  
FROM DataTable D  
JOIN cteB  
ON D.control_id = cteB.control_id  

Output:

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

Check an example:

;
with P as
(
    select *, row_number() over (partition by id order by parameterid) as i
    from ParameterTable
),
Q as
(
    select id, notes, final_notes = notes, j = 0, k = row_number() over (order by @@spid)
    from DataTable1
    union all
    select Q.id, Q.notes, replace(final_notes, '{<!-- -->{param = "' + P.parameterid + '"}}', p.value), j + 1, k
    from Q inner join P on P.id = Q.Id and P.i = Q.j + 1
)
select id, notes, final_notes
from Q t where j = (select max(j) from Q where k = t.k)
order by k

Answer 3

How about this one:

DECLARE @DataTable TABLE (
    id int,
    notes varchar(1000)
);
INSERT INTO @DataTable VALUES 
(1, 'develop document disseminate to {{param = "ac-1_prm_1"}}'),
(2, 'develop document  to {{param = "ac-1_prm_2"}}'),
(3, 'The Organization');

DECLARE @DataTableParameter TABLE (
    parameterid varchar(100),
    [value] varchar(100),
    id int
);
INSERT INTO @DataTableParameter VALUES
('ac-1_prm_1', 'apple doc.', 1),
('ac-1_prm_2', 'google doc.', 1),
('ac-1_prm_3', 'facebook doc.', 2);

;WITH CTE AS (
    SELECT 
        t.id,
        REPLACE(STUFF(
            (
                SELECT ', {{' + parameterid + '}}'
                FROM @DataTableParameter 
                WHERE id = t.id 
                ORDER BY parameterid
                FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'),
            1,
            1,
            ''
        ), ',', ' and ') AS parameters_in_notes,
        REPLACE(STUFF(
            (
                SELECT ', ' + [value]
                FROM @DataTableParameter 
                WHERE id = t.id 
                ORDER BY [value]
                FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'),
            1,
            1,
            ''
        ), ',', ' and ') AS [values]
    FROM @DataTableParameter AS t
    GROUP BY t.id
)

SELECT t1.id,
       CASE WHEN t2.id IS NOT NULL THEN LEFT(t1.notes, CHARINDEX('{', t1.notes) - 1) + t2.parameters_in_notes ELSE t1.notes END AS notes,
       CASE WHEN t2.id IS NOT NULL THEN LEFT(t1.notes, CHARINDEX('{', t1.notes) - 1) + t2.[values] ELSE '' END AS final_notes
FROM @DataTable AS t1
LEFT JOIN CTE AS t2
    ON t1.id = t2.id;

Answer 4

Here is a solution that uses the ability to call Python script in SQL Server. To be able to use this solutions, there are a few preconditions that needs to be fulfilled.

1. The Python support must be installed. This is an optional install. It can be added later.
2. The server configuration parameter external scripts enabled must be 1.
3. The user running the SQL code must have the database permission EXECUTE ANY EXTERNAL SCRIPT.

Since I was mainly interested in the general problem, to replace parameter placeholders in strings with data taken from a table, I simplified how the parameters are in the string. In the example below, a parameter appears simply as something enclosed in braces, for instance {param}. I leave it as an exercise to you to implement support for your more complex parameter holders. If you have worked with regular expressions in Python before, it should be that tricky.

DROP TABLE IF EXISTS DataTable
CREATE TABLE DataTable (
     id int,
     notes varchar(1000)
 );
 INSERT INTO DataTable VALUES 
 (1, 'develop document disseminate to {ac-1_prm_1} and {one_more_par} for the benefit of Mr Kite'),
 (2, 'develop document to {ac-1_prm_2}'),
 (3, 'The Organization {no_such_param} is winning the stakes');

 DECLARE @DataTableParameter TABLE (
     parameterid varchar(100),
     [value] varchar(100),
     id int
 );
 INSERT INTO @DataTableParameter VALUES
 ('ac-1_prm_1', 'apple doc.', 1),
 ('ac-1_prm_2', 'google doc.', 1),
 ('ac-1_prm_3', 'facebook doc.', 2),
 ('one_more_par', 'other sources', 3)

DECLARE @createdict nvarchar(MAX)
SELECT @createdict  = 'params = {' + 
            string_agg(quotename(parameterid, '''') + ':' + quotename(value, ''''), ', ') + '}'
FROM   @DataTableParameter

SELECT @createdict

DECLARE @pyscript nvarchar(MAX) = N'
import re, pandas

def paramrepl(matchobj):
    param = matchobj.group(1) 
    if param in params:
       return params[matchobj.group(1)]
    else:
       return "{" + param + "}"

' + @createdict + '

Data["notes"] = pandas.Series(
               [re.sub(r"\{([^\}]+)\}", paramrepl, str) for str in Data["notes"]])
'

EXEC sp_execute_external_script 
      @language           = N'Python',
      @input_data_1       = N'SELECT id, notes FROM DataTable',
      @input_data_1_name  = N'Data',
      @output_data_1_name = N'Data',
      @script             = @pyscript
WITH RESULT SETS ((id int,
                   notes varchar(1000)))