在 SQL 中的任何给定字符串中将字母替换为零 [英] Replacing alphabets to zero in any given string in SQL

查看：66
本文介绍了在 SQL 中的任何给定字符串中将字母替换为零的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在尝试将公式中的一些输入字符串替换为零值.我能够对某些字符串执行此操作，但无法对以下 #mathtemp 表中列出的所有字符串执行此操作.

我有输入名称，我有公式，我需要做的就是将没有数字(值)的字符串重新设置为零.替换语句中有一些我无法弄清楚的错误.

我尝试使用 ISNUMERIC 来完成此操作，但没有提供所需的输出.如果有任何新想法可以做到这一点或解决以下问题，请告诉我.

BEGIN将@INPUTCOUNT 声明为整数声明 @formulacount 为整数声明 @in 为 int声明 @ia 为 int声明 @OUTPUTFORMULATrade 为 nvarchar(160)设置@ia = 1设置@in = 1创建表#STATICFilter(IDNUM 整数标识(1,1),静态名称 Varchar(160),)插入 #STATICFILTER(静态名称)VALUES ('进口-消费海关价值(2266)') ,('进口-消费海关价值(1540)') ,('进口-消费海关价值(1541)')SET @INPUTCOUNT = (从 #STATICFILTER 中选择 count(*))创建表#MathTemp1(IDNUM 整数标识(1,1),年 VARCHAR(256),输出公式 VARCHAR(256),时间维度日期日期)INSERT INTO #MathTemp1 (YEARMONTH,OUTPUTFORMULA,Timedimensiondate)VALUES ('CV(N2) 1989: 1','2641.000 + 进口 - 消费海关价值(1540) + 进口 - 消费海关价值(1541)','1989-01-01'),('CV(N2) 1989: 10','54407.000 + 进口 - 消费海关价值(1540) + 63906.000','1989-10-01'),('CV(N2) 1990: 11','进口-消费海关价值(2266) + 进口-消费海关价值(1540) + 53088.000','1990-11-01'),('CV(N2) 1994: 5','32852.000 + 进口 - 消费海关价值(1540) + 进口 - 消费海关价值(1541)','1994-05-01')SET @formulacount = (从 #MathTemp1 中选择 count(*))而 (@ia < @formulacount)开始当(@in <@INPUTCOUNT)开始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结尾设置@ia = @ia + 1设置 @in =1选择@OUTPUTFORMULATrade AS New结尾删除表 #MathTemp1删除表#STATICFILTER结尾

请注意，#staticfilter 表中的输入以随机格式排列，类似地，#mathtemp 表也可能具有输入公式的任意组合.

我想要的输出是将任何给定公式中的字符串替换为零值.请检查以下

2641.000 + 0 + 054407.000 + 0 + 63906.0000 + 0 + 53088.000

解决方案

这需要结合一些先进的技术来做到这一点.第一个问题是您有分隔数据.当您将多个值塞入单个单元格时，这违反了 1NF.第二个难题是如何将这些数据透视为动态数量的列.SO 周围的大多数人更喜欢使用动态 PIVOT.我更喜欢使用动态交叉表.我发现语法不那么笨拙，它甚至比动态交叉表的性能还要高一点.

您可以在此处阅读有关我通常使用的拆分器的信息.http://www.sqlservercentral.com/articles/Tally+Table/72993/ 此拆分器提供的大多数其他拆分器没有的主要优点是它返回值列表中项目的行号.这对于这种情况非常有用.如果您真的想深入了解分离器世界，这里还有其他几个不错的选择.http://sqlperformance.com/2012/07/t-sql-queries/split-strings

您可以在此处阅读有关动态交叉表的更多信息.http://www.sqlservercentral.com/articles/Crosstab/65048/>

我真的不明白#STATICFILTER 表与此有什么关系，所以我只是忽略了它.

在实施之前，请确保您理解此代码.参考文章详细介绍了这些技术.

如果 OBJECT_ID('tempdb..#MathTemp1') 不为空删除表 #MathTemp1创建表#MathTemp1(IDNUM 整数标识(1,1),年 VARCHAR(256),输出公式 VARCHAR(256),时间维度日期日期)INSERT INTO #MathTemp1 (YEARMONTH,OUTPUTFORMULA,Timedimensiondate)VALUES ('CV(N2) 1989: 1','2641.000 + 进口 - 消费海关价值(1540) + 进口 - 消费海关价值(1541)','1989-01-01'),('CV(N2) 1989: 10','54407.000 + 进口 - 消费海关价值(1540) + 63906.000','1989-10-01'),('CV(N2) 1990: 11','进口-消费海关价值(2266) + 进口-消费海关价值(1540) + 53088.000','1990-11-01'),('CV(N2) 1994: 5','32852.000 + 进口 - 消费海关价值(1540) + 进口 - 消费海关价值(1541)','1994-05-01')声明@StaticPortion nvarchar(2000) ='以 OrderedResults 作为(选择 mt.IDNUM, mt.OutputFormula, mt.Timedimensiondate, mt.YEARMONTH, x.ItemNumber, LTRIM(RTRIM(x.Item)) 作为项目来自 #MathTemp1 米交叉应用 dbo.DelimitedSplit8K(mt.OutputFormula, ''+'') x)选择 IDNUM';声明@DynamicPortion nvarchar(max) = '';声明@FinalStaticPortion nvarchar(2000) = ' from OrderedResults Group by IDNUM order by IDNUM';使用 E1(N) AS(从 (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 或 100 行E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 或 10,000 行最大cteTally(N) AS(SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4)选择@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 0end end) as Value' + CAST(N as varchar(6)) + CHAR(10)来自 cteTally t其中 t.N <=(选择 MAX(LEN(OutputFormula) - LEN(replace(OutputFormula, '+', ''))) + 1来自#MathTemp1)声明@SqlToExecute nvarchar(max) = @StaticPortion + @DynamicPortion + @FinalStaticPortion;--选择@SqlToExecuteexec sp_executesql @SqlToExecute

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

解决方案

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

这篇关于在 SQL 中的任何给定字符串中将字母替换为零的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆