PATINDEX索引列的所有值

I'm making a query that will delete all rows from table1 that has its column table1.id = table2.id

我正在创建一个查询，它将从表1中删除所有列为table1的行。id = table2.id

table1.id column is in nvarchar(max) with an xml format like this:

表1。id列在nvarchar(max)中，xml格式如下:

<customer><name>Paulo</name><gender>Male</gender><id>12345</id></customer>

EDIT: The id column is just a part of a huge XML so the ending tag may not match the starting tag.

编辑:id列只是大型XML的一部分，因此结束标记可能与开始标记不匹配。

I've tried using name.nodes but it only applies to xml columns and changing the column datatype is not a choice, So far this is the my code using PATINDEX

我尝试使用的名字。节点，但它只适用于xml列，更改列数据类型不是一种选择，到目前为止，这是使用PATINDEX的my代码

DELETE t1
FROM table1 t1
WHERE PATINDEX('%12345%',id) != 0

But what I need is to search for all values from table2.id which contains like this:

但我需要从表2中搜索所有的值。id包含如下内容:

Any approach would be nice like sp_executesql and/or while loop, or is there a better approach than using patindex? thanks!

任何方法都很好，比如sp_executesql和/或while循环，或者是否有比使用patindex更好的方法?谢谢!

2 个解决方案

#1

Select *
--Delete A
 From Table1 A
 Join Table2 B on CharIndex('id>'+SomeField+'<',ID)>0

I don't know the name of the field in Table2. I am also assuming it is a varchar. If not, cast(SomeField as varchar(25))

我不知道表2中的字段名。我还假设它是一个varchar。如果不是，则cast(如varchar(25))

EDIT - This is what I tested. It should work

编辑-这是我测试的。它应该工作

Declare @Table1 table (id varchar(max))
Insert Into @Table1 values
('<customer><name>Paulo</name><gender>Male</gender><id>12345</id></customer>'),
('<customer><name>Jane</name><gender>Femail</gender><id>7895</id></customer>')

Declare @Table2 table (SomeField varchar(25))
Insert into @Table2 values
('12345'),
('67890'),
('10000'),
('20000'),
('30000')


Select *
--Delete A
 From @Table1 A
 Join @Table2 B on CharIndex('id>'+SomeField+'<',ID)>0

#2

;with cteBase as (
    Select *,XMLData=cast(id as xml) From Table1
)
Select *
 From cteBase
 Where XMLData.value('(customer/id)[1]','int') in (12345,67890,10000,20000,30000)

If you are satisfied with the results, change the final Select * to Delete

如果您对结果感到满意，请将最终的Select *更改为Delete

#1

Select *
--Delete A
 From Table1 A
 Join Table2 B on CharIndex('id>'+SomeField+'<',ID)>0