sql-server - 如果找不到多个实例,则强制 CharIndex 仅返回 0?
问题描述
我的 Row_1 值为
<FeeSize><XMLs><XML Character="1.11" MaxXML="249999.99" Fee="1.60" Cost="1.8" Size="1.8"/><XML Character="251111.11" MaxXML="499999.99" Fee="1.40" Cost="1.7" Size="1.7"/><XML Character="511111.11" MaxXML="999999.99" Fee="1.20" Cost="1.6" Size="1.6"/><XML Character="1111111.11" MaxXML="1999999.99" Fee="1.11" Cost="1.5" Size="1.5"/><XML Character="2111111.11" MaxXML="4999999.99" Fee="1.90" Cost="1.45" Size="1.45"/><XML Character="5111111.11" MaxXML="9999999999999.99" Fee="1.85" Cost="1.425" Size="1.425"/></XMLs></FeeSize>
我的 Row_2 值为
<FeeSize><XMLs><XML Character="1.11" MaxXML="249999.99" Fee="1.60" Cost="1.8" Size="1.8"/></XMLs></FeeSize>
我的要求是识别记录中字符串“Character”的所有起始位置。
我使用的查询是
CHARINDEX('Character', Record, 1) First_C,
CHARINDEX('Character', Record, CHARINDEX('Character', Record)+1) Second_C,
CHARINDEX('Character', Record, CHARINDEX('Character', Record,CHARINDEX('Character', Record)+1)+1) Third_C,
CHARINDEX('Character', Record, CHARINDEX('Character', Record,CHARINDEX('Character', Record, CHARINDEX('Character', Record)+1)+1)+1) Fourth_C,
CHARINDEX('Character', Record, CHARINDEX('Character', Record,CHARINDEX('Character', Record, CHARINDEX('Character', Record,CHARINDEX('Character', Record)+1)+1)+1)+1) Fifth_C,
CHARINDEX('Character', Record, CHARINDEX('Character', Record,CHARINDEX('Character', Record, CHARINDEX('Character', Record,CHARINDEX('Character', Record, CHARINDEX('Character', Record)+1)+1)+1)+1)+1) Sixth_C
现在我可以从 Row_1 看到“字符”的所有起始位置。
由于 Row_2 只有一个“Character”实例,我应该只在第一列 First_C 获得位置。但是,我在第三列 Third_C 和第五列 Fifth_C 获得了第一个起始位置值,即使不应该有任何值。
如何为 Row_2 的第二到六列获得“0”?
解决方案
我确信有更好的方法可以做到这一点,但以下代码将为您提供所需的结果。
Use AdventureWorks2017;
Go
Declare @xml1 nvarchar(max)
Set @xml1 ='<FeeSize><XMLs><XML Character="1.11" MaxXML="249999.99" Fee="1.60" Cost="1.8" Size="1.8"/><XML Character="251111.11" MaxXML="499999.99" Fee="1.40" Cost="1.7" Size="1.7"/><XML Character="511111.11" MaxXML="999999.99" Fee="1.20" Cost="1.6" Size="1.6"/><XML Character="1111111.11" MaxXML="1999999.99" Fee="1.11" Cost="1.5" Size="1.5"/><XML Character="2111111.11" MaxXML="4999999.99" Fee="1.90" Cost="1.45" Size="1.45"/><XML Character="5111111.11" MaxXML="9999999999999.99" Fee="1.85" Cost="1.425" Size="1.425"/></XMLs></FeeSize>'
Declare @xml2 nvarchar(max)
Set @xml2 = '<FeeSize><XMLs><XML Character="1.11" MaxXML="249999.99" Fee="1.60" Cost="1.8" Size="1.8"/></XMLs></FeeSize>'
If Object_ID ('tempdb..#Six') is not null Drop Table #Six
Create Table #Six (id int identity (1,1), xml_1 nvarchar(max), xml_2 nvarchar(max))
Insert into #Six (xml_1, xml_2)
Select @xml1, @xml2 UNION ALL
Select @xml1, @xml2 UNION ALL
Select @xml1, @xml2 UNION ALL
Select @xml1, @xml2 UNION ALL
Select @xml1, @xml2 UNION ALL
Select @xml1, @xml2
Select case
when id = 1 then charindex('Character',xml_1,1)
when id = 2 and charindex('Character',xml_1,1) <> 0
then charindex('Character',xml_1,charindex('Character',xml_1,1)+1)
when id = 3 and charindex('Character',xml_1,charindex('Character',xml_1,1)+1) <> 0
then charindex('Character',xml_1,charindex('Character',xml_1,charindex('Character',xml_1,charindex('Character',xml_1,1)+1)+1)+1)
when id = 4 and charindex('Character',xml_1,charindex('Character',xml_1,charindex('Character',xml_1,charindex('Character',xml_1,1)+1)+1)+1) <> 0
then charindex('Character',xml_1,charindex('Character',xml_1,charindex('Character',xml_1,charindex('Character',xml_1,charindex('Character',xml_1,1)+1)+1)+1)+1)
when id = 5 and charindex('Character',xml_1,charindex('Character',xml_1,charindex('Character',xml_1,charindex('Character',xml_1,charindex('Character',xml_1,1)+1)+1)+1)+1) <> 0
then charindex('Character',xml_1,charindex('Character',xml_1,charindex('Character',xml_1,charindex('Character',xml_1,charindex('Character',xml_1,charindex('Character',xml_1,1)+1)+1)+1)+1)+1)
when id = 6 and charindex('Character',xml_1,charindex('Character',xml_1,charindex('Character',xml_1,charindex('Character',xml_1,charindex('Character',xml_1,charindex('Character',xml_1,1)+1)+1)+1)+1)+1) <> 0
then charindex('Character',xml_1,charindex('Character',xml_1,charindex('Character',xml_1,charindex('Character',xml_1,charindex('Character',xml_1,charindex('Character',xml_1,charindex('Character',xml_1,1)+1)+1)+1)+1)+1)+1)
ELSE 0 end as cascaded_xml1,
case
when id = 1 then charindex('Character',xml_2,1)
when id = 2 and charindex('Character',xml_2,1) <> 0
then charindex('Character',xml_2,charindex('Character',xml_2,1)+1)
when id = 3 and charindex('Character',xml_2,charindex('Character',xml_2,1)+1) <> 0
then charindex('Character',xml_2,charindex('Character',xml_2,charindex('Character',xml_2,charindex('Character',xml_2,1)+1)+1)+1)
when id = 4 and charindex('Character',xml_2,charindex('Character',xml_2,charindex('Character',xml_2,charindex('Character',xml_2,1)+1)+1)+1) <> 0
then charindex('Character',xml_2,charindex('Character',xml_2,charindex('Character',xml_2,charindex('Character',xml_2,charindex('Character',xml_2,1)+1)+1)+1)+1)
when id = 5 and charindex('Character',xml_2,charindex('Character',xml_2,charindex('Character',xml_2,charindex('Character',xml_2,charindex('Character',xml_2,1)+1)+1)+1)+1) <> 0
then charindex('Character',xml_2,charindex('Character',xml_2,charindex('Character',xml_2,charindex('Character',xml_2,charindex('Character',xml_2,charindex('Character',xml_2,1)+1)+1)+1)+1)+1)
when id = 6 and charindex('Character',xml_2,charindex('Character',xml_2,charindex('Character',xml_2,charindex('Character',xml_2,charindex('Character',xml_2,charindex('Character',xml_2,1)+1)+1)+1)+1)+1) <> 0
then charindex('Character',xml_2,charindex('Character',xml_2,charindex('Character',xml_2,charindex('Character',xml_2,charindex('Character',xml_2,charindex('Character',xml_2,charindex('Character',xml_2,1)+1)+1)+1)+1)+1)+1)
ELSE 0 end as cascaded_xml2
from #Six
您的代码中发生的情况是级联的 charindex 被传入,它实际上是 = 0,这将 SQL 放回字符串的开头附近,准备好查找“Character”的新实例。这是相当困难的。上面的代码可以工作,但我确信,有更好的方法可以做到这一点。
推荐阅读
- github - 如何在允许提交之前对 GitHub 分支强制执行代码质量规则?
- mysql - 为什么在执行此 SQL 查询时会出现重复的列?
- pyspark - ADLS Gen 1 中 DELTA 格式文件的外部表
- android - FLUTTER:当 image_picker 添加为依赖项时,发布安装中缺少插件
- excel - 如何在两列中使用 excel 函数或 VBA 获取准确值
- arduino - 主机名以“.local”结尾的 ESP8266 DNS 查找
- cypress - Cypress 测试点击另一个页面中的按钮后返回登录页面
- python - 查找源图像点opencv python的坐标
- python - 生命线上的 Cox PH 显示收敛问题
- html - 如何在响应式设计中使用负边距?