SQL 查询:删除长 varchar(max) 列中的多个字符串

Question

我在varchar(max(列中有以下字符串：

PREV - FirstName: John / LAST - FirstName: Johan; PREV- LastName: Crescot / LAST - LastName: Crescott;

在每个分号之后，可以出现无穷无尽的 PREV 值和 LAST 值突变，具体取决于源系统中所做的更改量。

我需要编写一个仅返回 PREV 值的查询。对于上面的字符串，所需的结果将是：

FirstName: John; LastName: Crescot

所有斜杠 (/( 分隔符和破折号也需要删除，如所需结果所示。

谁能帮我解决这个问题？谢谢大家！

Answer 1

如果对 UDF 开放，请考虑以下事项。

厌倦了提取字符串(charindindex，patindex，left，right...(，我修改了一个解析函数以接受两个非同类参数。 在本例中为"PREV"和"/">

例

Declare @YourTable table (ID int,SomeCol varchar(max))
Insert Into @YourTable values
(1,'PREV - FirstName: John / LAST - FirstName: Johan; PREV- LastName: Crescot / LAST - LastName: Crescott;')
Select A.ID
,B.NewVal 
From  @YourTable A
Cross Apply (
Select NewVal = Stuff((Select '; '+ltrim(rtrim(replace(RetVal,'-','')))
From  [dbo].[udf-Str-Extract](A.SomeCol,'PREV','/') 
For  XML Path ('')),1,2,'') 
) B

返回

ID  NewVal
1   FirstName: John; LastName: Crescot

UDF 如果有兴趣

CREATE FUNCTION [dbo].[udf-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].[udf-Str-Extract] (@String,'[[',']]')
*/

Answer 2

create table #temp(val varchar(max))
Insert into #temp values('PREV - FirstName: John / LAST - FirstName: Johan; PREV - LastName: Crescot / LAST - LastName')
Select stuff(
(SELECT ';'+ 
Replace(stuff(Tbl.Col.value('./text()[1]','varchar(50)'),charindex('/',Tbl.Col.value('./text()[1]','varchar(50)')),len(Tbl.Col.value('./text()[1]','varchar(50)')),''),'PREV -','')as ColName
FROM 
(Select cast('<a>'+ replace((SELECT val As [*] FOR XML PATH('')), ';', '</a><a>') + '</a>' as xml)as t 
from #temp) tl
Cross apply 
tl.t.nodes('/a') AS Tbl(Col) for xml path(''),type).value('.','NVARCHAR(MAX)'),1,2,'')

此方法不需要任何其他 UDF。 分解上述查询以便于理解： 1.根据分号";"将一行字符串转换为多行

SELECT 
Tbl.Col.value('./text()[1]','varchar(50)')
FROM 
(Select cast('<a>'+ replace((SELECT val As [*] FOR XML PATH('')), ';', '</a><a>') + '</a>' as xml)as t 
from #temp) tl
Cross apply 
tl.t.nodes('/a') AS Tbl(Col)

2.在上述提取值上，使用替换和填充命令删除不必要的字符

SELECT 
Replace(stuff(Tbl.Col.value('./text()[1]','varchar(50)'),charindex('/',Tbl.Col.value('./text()[1]','varchar(50)')),len(Tbl.Col.value('./text()[1]','varchar(50)')),''),'PREV -','')as ColName
FROM 
(Select cast('<a>'+ replace((SELECT val As [*] FOR XML PATH('')), ';', '</a><a>') + '</a>' as xml)as t 
from #temp) tl
Cross apply 
tl.t.nodes('/a') AS Tbl(Col)

3.根据需要使用stuff和xml路径将多行恢复为用分号分隔的单行

Select stuff(
(SELECT ';'+ 
Replace(stuff(Tbl.Col.value('./text()[1]','varchar(50)'),charindex('/',Tbl.Col.value('./text()[1]','varchar(50)')),len(Tbl.Col.value('./text()[1]','varchar(50)')),''),'PREV -','')as food_Name
FROM 
(Select cast('<a>'+ replace((SELECT val As [*] FOR XML PATH('')), ';', '</a><a>') + '</a>' as xml)as t 
from #temp) tl
Cross apply 
tl.t.nodes('/a') AS Tbl(Col) for xml path(''),type).value('.','NVARCHAR(MAX)'),1,2,'')