Replacing alphabets to zero in any given string in SQL

Question

I'm trying to replace some input strings in a formula to zero value. I was able to do it for certain strings but wasn't able to do for all the strings listed in the below #mathtemp table.

I have the input names, I have the formula and all I need to do is just repalce the strings which doesn't have numbers(values) to zero. There is some mistake in the replace statement which I am not able to figure out.

I tried using ISNUMERIC to get this done but that doesn't give the required output. Let me know if there is any new idea to do this or to solve the below one.

BEGIN
DECLARE @INPUTCOUNT AS INTEGER
DECLARE @formulacount as integer
DECLARE @in as int
DECLARE @ia as int
DECLARE @OUTPUTFORMULATrade as nvarchar(160)
set @ia = 1
set @in = 1



CREATE TABLE #STATICFILTER
(
IDNUM INTEGER IDENTITY(1,1),
STATICNAME Varchar(160),
)

INSERT INTO #STATICFILTER (STATICNAME)
VALUES ('Import - Consumption customs value(2266)') ,('Import - Consumption customs value(1540)') ,('Import - Consumption customs value(1541)')


SET @INPUTCOUNT = (select count(*)  from #STATICFILTER)

CREATE TABLE #MathTemp1
(
IDNUM INTEGER IDENTITY(1,1),
YEARMONTH VARCHAR(256),
OUTPUTFORMULA VARCHAR(256),
Timedimensiondate Date
)

INSERT INTO #MathTemp1 (YEARMONTH,OUTPUTFORMULA,Timedimensiondate)
VALUES ('CV(N2)  1989: 1','2641.000 + Import - Consumption customs value(1540) + Import - Consumption customs value(1541)','1989-01-01')
,('CV(N2)  1989: 10','54407.000 + Import - Consumption customs value(1540) + 63906.000','1989-10-01')
,('CV(N2)  1990: 11','Import - Consumption customs value(2266) + Import - Consumption customs value(1540) + 53088.000','1990-11-01')
,('CV(N2)  1994: 5','32852.000 + Import - Consumption customs value(1540) + Import - Consumption customs value(1541)','1994-05-01')
SET @formulacount = (select count(*)  from #MathTemp1)

while (@ia < @formulacount)
begin
WHILE (@in < @INPUTCOUNT)
BEGIN
SET @OUTPUTFORMULATrade = (Select Replace ((SELECT REPLACE(OUTPUTFORMULA,(select STATICNAME from #STATICFILTER where IDNUM = @in),0)FROM #MathTemp1 WHERE IDNUM = @ia),(select STATICNAME from #STATICFILTER where IDNUM = @in+1),0))
SET @in = @in + 1
END
SET @ia = @ia + 1
SET @in  =1
Select @OUTPUTFORMULATrade AS New 
END
drop table #MathTemp1
drop table #STATICFILTER

END

please note the inputs are arranged in a random format in #staticfilter table and similarly #mathtemp table might also have any combination of input formulas.

My desired output is to replace the strings in any given formula to zero vlaues. please check below

2641.000 + 0 + 0
54407.000 + 0 + 63906.000
0 + 0 + 53088.000

What are you expecting for output from your sample data? The nested while loop approach does not seem like a good way to handle this. — Sean Lange, Jul 11 '16 at 18:30
@dcod regex and sql server have traditionally never been associated except via CLR — Jeremy Holovacs, Jul 11 '16 at 18:32
@SeanLange : Please check my edits where I have included the desired output.... — Gowtham Ramamoorthy, Jul 11 '16 at 18:41
@GowthamRamamoorthy What about the answer here: http://stackoverflow.com/a/23001916/5165560 — dcod, Jul 11 '16 at 19:18

score 1 · Accepted Answer · answered Jul 11 '16 at 20:10

This takes a number of somewhat advanced techniques combined to do this. The first problem is you have delimited data. This violates 1NF when you cram multiple values into a single cell. The second piece of the puzzle is how to PIVOT this data into a dynamic number of columns. Most people around SO prefer to use a dynamic PIVOT. I prefer to use a dynamic cross tab instead. I find the syntax less obtuse and it is even a little bit more performant than a dynamic cross tab.

You can read about the splitter I typically use here. http://www.sqlservercentral.com/articles/Tally+Table/72993/ The main advantage this splitter offers that most others don't is that it returns the row number of the item within the list of values. This is incredibly useful for this type of situation. If you really want to dive into the splitter world here are several other excellent options. http://sqlperformance.com/2012/07/t-sql-queries/split-strings

You can read more about Dynamic cross tabs here. http://www.sqlservercentral.com/articles/Crosstab/65048/

I don't really understand what the #STATICFILTER table has to do with this so I just ignored it.

Make sure you understand this code before you implement it. The referenced articles go into great detail about these techniques.

if OBJECT_ID('tempdb..#MathTemp1') is not null
    drop table #MathTemp1

CREATE TABLE #MathTemp1
(
    IDNUM INTEGER IDENTITY(1,1),
    YEARMONTH VARCHAR(256),
    OutputFormula VARCHAR(256),
    Timedimensiondate Date
)

INSERT INTO #MathTemp1 (YEARMONTH,OUTPUTFORMULA,Timedimensiondate)
VALUES ('CV(N2)  1989: 1','2641.000 + Import - Consumption customs value(1540) + Import - Consumption customs value(1541)','1989-01-01')
,('CV(N2)  1989: 10','54407.000 + Import - Consumption customs value(1540) + 63906.000','1989-10-01')
,('CV(N2)  1990: 11','Import - Consumption customs value(2266) + Import - Consumption customs value(1540) + 53088.000','1990-11-01')
,('CV(N2)  1994: 5','32852.000 + Import - Consumption customs value(1540) + Import - Consumption customs value(1541)','1994-05-01')

declare @StaticPortion nvarchar(2000) = 
    'with OrderedResults as
    (   
        select mt.IDNUM
            , mt.OutputFormula
            , mt.Timedimensiondate
            , mt.YEARMONTH
            , x.ItemNumber
            , LTRIM(RTRIM(x.Item)) as Item
        from #MathTemp1 mt
        cross apply dbo.DelimitedSplit8K(mt.OutputFormula, ''+'') x
    )
    Select IDNUM';

declare @DynamicPortion nvarchar(max) = '';
declare @FinalStaticPortion nvarchar(2000) = ' from OrderedResults Group by IDNUM order by IDNUM';  

with E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS 
(
    SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)

select @DynamicPortion = @DynamicPortion + 
    ', MAX(Case when ItemNumber = ' + CAST(N as varchar(6)) + 'then case when ISNUMERIC(Item) = 1 then convert(numeric(9,3), ltrim(rtrim(Item))) else 0 end end) as Value' + CAST(N as varchar(6)) + CHAR(10)
from cteTally t
where t.N <= 
(
    select MAX(LEN(OutputFormula) - LEN(replace(OutputFormula, '+', ''))) + 1
    from #MathTemp1
)


declare @SqlToExecute nvarchar(max) = @StaticPortion + @DynamicPortion + @FinalStaticPortion;

--select @SqlToExecute
exec sp_executesql @SqlToExecute

thanks a lot man... you saved most of my time..... I ll check and vote this one... — Gowtham Ramamoorthy, Jul 11 '16 at 20:12

Replacing alphabets to zero in any given string in SQL

1 Answers1

Linked