我有一个文本归档，我需要从左侧获取字符串的一部分。
select top 100 Description, right(Description,
charindex('</span>', reverse(Description))-1)
as CleanedDesc
from MyDB.dbo.MyTable_output
当运行查询时，如果尾部出现以下错误：
传递给 RIGHT 函数的长度参数无效。
这实际上是正确的，因为我charindex('</span>'
这是不对的，它必须是单个字符，但不幸的是，但文本值"分隔符"正是.
下面是可用于测试的字符串数据示例：
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <title></title> </head> <body> <span class="added">Added: <input type="hidden" name="dnf_class_values[procurement_notice][description][0][added_on]" value="2017-06-19 10:32:17">Jun 19, 2017 10:32 am</span> This solicitation will contain NSN 1d2323, OIL. A Request for Proposal (RFP) will be issued for award .....ed item and must be acquired from an approved manufacturer. The approved sources are:<br> SEYER INDUSTRIES, INC. 19494 P/N 74A150625-1006<br>
我找到了解决方法。那就是使用 CTE 将"替换为我确保"描述"列中不存在的字符。我使用此代码扫描了我的表格
select * from MyDB.dbo.MyTable_output where Description like '%~%'.
但我确实有一些记录确实具有此值"~"。所以这不是一个选择。
但即使我找不到任何从长远来看似乎不切实际的解决方案
;with cte as (
select top 100 RowNo, replace(Description, '</span>','~') as ReplacedDesc from InputTemp.dbo.SearchFBO_output
)
select
RowNo = ROW_NUMBER() OVER (ORDER BY EntityPKID),
Project,
[Title],
RIGHT(cte.ReplacedDesc, charindex('~', reverse(cte.ReplacedDesc))-1) as Description,
FROM
MyDB.dbo.MyTable_output o
INNER JOIN cte ON cte.RowNo = o.RowNo
不是 100% 确定这是否是您要查找的，但您可以使用分隔符。
如果对表值函数开放，请考虑以下事项：
厌倦了提取数据...左，右，字符索引，反向，...我修改了一个解析函数以接受两个非类似分隔符(最多 100 个字符而不是一个(。
示例或 dbFiddle
Declare @YourTable table ([ID] int,[Description] varchar(max))
Insert Into @YourTable values
(1,'<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <title></title> </head> <body> <span class="added">Added: <input type="hidden" name="dnf_class_values[procurement_notice][description][0][added_on]" value="2017-06-19 10:32:17">Jun 19, 2017 10:32 am</span> This solicitation will contain NSN 1d2323, OIL. A Request for Proposal (RFP) will be issued for award .....ed item and must be acquired from an approved manufacturer. The approved sources are:<br> SEYER INDUSTRIES, INC. 19494 P/N 74A150625-1006<br>')
Select A.ID
,B.*
From @YourTable A
Cross Apply [dbo].[tvf-Str-Extract]([Description],'>','</span') B
Where RetVal>''
返回
ID RetSeq RetPos RetVal
1 1 262 Jun 19, 2017 10:32 am
现在，如果分律计并<，则将返回以下内容
ID RetSeq RetPos RetVal
1 7 136 Added:
1 8 262 Jun 19, 2017 10:32 am
1 9 290 This solicitation will contain NSN 1d2323, OIL. A Request for Proposal (RFP) will be issued for award .....ed item and must be acquired from an approved manufacturer. The approved sources are:
1 10 487 SEYER INDUSTRIES, INC. 19494 P/N 74A150625-1006
UDF 如果有兴趣
CREATE FUNCTION [dbo].[tvf-Str-Extract] (@String varchar(max),@Delimiter1 varchar(100),@Delimiter2 varchar(100))
Returns Table
As
Return (
with cte1(N) As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
cte2(N) As (Select Top (IsNull(DataLength(@String),0)) Row_Number() over (Order By (Select NULL)) From (Select N=1 From cte1 N1,cte1 N2,cte1 N3,cte1 N4,cte1 N5,cte1 N6) A ),
cte3(N) As (Select 1 Union All Select t.N+DataLength(@Delimiter1) From cte2 t Where Substring(@String,t.N,DataLength(@Delimiter1)) = @Delimiter1),
cte4(N,L) As (Select S.N,IsNull(NullIf(CharIndex(@Delimiter1,@String,s.N),0)-S.N,8000) From cte3 S)
Select RetSeq = Row_Number() over (Order By N)
,RetPos = N
,RetVal = left(RetVal,charindex(@Delimiter2,RetVal)-1)
From (
Select *,RetVal = Substring(@String, N, L)
From cte4
) A
Where charindex(@Delimiter2,RetVal)>1
)
/*
Max Length of String 1MM characters
Declare @String varchar(max) = 'Dear [[FirstName]] [[LastName]], ...'
Select * From [dbo].[tvf-Str-Extract] (@String,'[[',']]')
*/
编辑 - 关于评论
您可能会注意到我添加了一个愚蠢的结尾分隔符|||
Select A.ID
,B.*
From @YourTable A
Cross Apply [dbo].[tvf-Str-Extract]([Description]+'|||','</span>','|||') B
Where RetVal>''