Replacing values in a string using recursion in SQL
<p>I'm trying to repalce values into a string based on the column 'Yearmonth' and 'Timedimensiondate' in the 'Mathlogictable' shown in below code....</p>

<p>I was able to do it successfully but now I need to replace the string with zero values which are not there in the Mathlogictable.</p>

<p>For eg: if you execute the below code we can find the first row having values for import-consumptionqty1(314) but not for import consumtionQty(1539)..... like</p>

<pre><code>  YEARMONTH         OUTPUTFORMULA                                   TIMEDIMENSIONDATE                 
  CV(N1)  2002: 10  236846.000 + Import - Consumption qty1(1539)    2002-10-01
</code></pre>

<p>I need to replace the strings which doesn't have values to zero in the formula column...... such that my output should be like </p>

<pre><code>Yearmonth           Formula         TimeDimensionDate      
CV(N1)  2002: 10    236846.000 + 0  2002-10-01
</code></pre>

<p>How can I achieve this ?</p>

<p>Please note that i have created a tem table called #tablevaluedfunction and inserted the output in that table.... and made a join.....</p>

<p>Below is the code i'm trying</p>

<pre><code>Begin
CREATE TABLE #MathLogicTable
(
IDNUM INTEGER IDENTITY(1,1),
FORMULA Varchar(160),
INPUTName varchar(160),
AttributeValue Decimal(15,3),
yearmonth varchar(160),
TimeDimensionDate date
)

INSERT INTO #MathLogicTable (FORMULA,INPUTName,AttributeValue,yearmonth,TimeDimensionDate)
VALUES ('Import - Consumption qty1(314) + Import - Consumption qty1(1539)','Import - Consumption qty1(1539)',855,'CV(N1)  2002: 1','2002-01-01')
,('Import - Consumption qty1(314) + Import - Consumption qty1(1539)','Import - Consumption qty1(1539)',4860,'CV(N1)  2002: 2','2002-02-01')
,('Import - Consumption qty1(314) + Import - Consumption qty1(1539)','Import - Consumption qty1(1539)',21683,'CV(N1)  2002: 3','2002-03-01')
,('Import - Consumption qty1(314) + Import - Consumption qty1(1539)','Import - Consumption qty1(1539)',12800,'CV(N1)  2002: 4','2002-04-01')
,('Import - Consumption qty1(314) + Import - Consumption qty1(1539)','Import - Consumption qty1(1539)',802,'CV(N1)  2002: 5','2002-05-01')
,('Import - Consumption qty1(314) + Import - Consumption qty1(1539)','Import - Consumption qty1(314)',250616,' CV(N1)  2002: 6 ','2002-06-01')
,('Import - Consumption qty1(314) + Import - Consumption qty1(1539)','Import - Consumption qty1(314)',333064,' CV(N1)  2002: 7 ','2002-07-01')
,('Import - Consumption qty1(314) + Import - Consumption qty1(1539)','Import - Consumption qty1(314)',131397,' CV(N1)  2002: 8 ','2002-08-01')
,('Import - Consumption qty1(314) + Import - Consumption qty1(1539)','Import - Consumption qty1(314)',294510,' CV(N1)  2002: 9 ','2002-09-01')
,('Import - Consumption qty1(314) + Import - Consumption qty1(1539)','Import - Consumption qty1(314)',236846,' CV(N1)  2002: 10 ','2002-10-01')
,('Import - Consumption qty1(314) + Import - Consumption qty1(1539)','Import - Consumption qty1(314)',397183,' CV(N1)  2002: 11 ','2002-11-01')
,('Import - Consumption qty1(314) + Import - Consumption qty1(1539)','Import - Consumption qty1(314)',128616,' CV(N1)  2002: 12 ','2002-12-01')

;WITH cteSplitFormula AS (
    SELECT
       IDNUM
       ,TimeDimensionDate
       ,CAST(LTRIM(RTRIM(LEFT(FORMULA, PATINDEX('%[+\*/^]%',FORMULA+'+')-1))) AS VARCHAR(5000)) AS FormulaItem
       ,STUFF(FORMULA, 1, PATINDEX('%[+\*/^]%',FORMULA+'+'), '')  as RemainingFromula
    FROM
       #MathLogicTable

    UNION ALL
    SELECT
       IDNUM
       ,TimeDimensionDate
       ,CAST(LTRIM(RTRIM(LEFT(RemainingFromula, PATINDEX('%[+\*/^]%',RemainingFromula+'+')-1))) AS VARCHAR(5000)) AS FormulaItem
       ,STUFF(RemainingFromula, 1, PATINDEX('%[+\*/^]%',RemainingFromula+'+'), '')  as RemainingFromula
    FROM
       cteSplitFormula
    WHERE 
       LEN(RemainingFromula) &gt; 0

)

, ctePartsValues AS (
    SELECT
       sf.IDNUM
       ,sf.TimeDimensionDate
       ,sf.FormulaItem AS FormulaItem
       ,ISNULL(m.AttributeValue,0) AS ReplacementValue
       ,ROW_NUMBER() OVER(PARTITION BY sf.IDNUM ORDER BY (SELECT 1)) AS [ReplacementOrderID]
    FROM
       cteSplitFormula sf
       FULL OUTER JOIN #MathLogicTable m
       ON sf.FormulaItem = LTRIM(RTRIM(m.INPUTName))
       AND sf.TimeDimensionDate = m.TimeDimensionDate
)

, cteRecursiveDataSource AS (
    SELECT
       m.IDNUM
       ,REPLACE(m.Formula,pv.FormulaItem,pv.ReplacementValue) as Formula
       ,pv.ReplacementOrderID
    FROM
       #MathLogicTable m
       INNER JOIN ctePartsValues pv
       ON m.IDNUM = pv.IDNUM
       AND pv.ReplacementOrderID = 1

    UNION ALL

    SELECT
       rds.IDNUM
       ,REPLACE(rds.Formula,pv.FormulaItem,pv.ReplacementValue) as Formula
       ,pv.ReplacementOrderID
    FROM
       cteRecursiveDataSource rds
       INNER JOIN ctePartsValues pv
       ON rds.IDNUM = pv.IDNUM
       AND rds.ReplacementOrderID + 1 = pv.ReplacementOrderID
)

, cteIdentifyRowWithLastReplacementStatement AS (
    SELECT
       *
       ,ROW_NUMBER() OVER (PARTITION BY IDNUM ORDER BY ReplacementOrderId DESC) as FinalRow
    FROM
       cteRecursiveDataSource
)

SELECT *
FROM
    cteIdentifyRowWithLastReplacementStatement
WHERE
    FinalRow = 1


DROP table #MathLogicTable
END
</code></pre>

<p>Thanks</p>