PATINDEX all values of a column

Question

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

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

<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.

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

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:

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

John Cappelletti · Answer 1 · 2016-09-16T03:16:38.373

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

edited Sep 16 '16 at 03:16

answered Sep 16 '16 at 02:46

John Cappelletti

79,615
7
44
66

Thank you so much. But unfortunately my id column is not a complete XML format. It is only a part of a huge XML so it throws an error: `XML parsing: line 1, character 27, end tag does not match start tag` Is there any other way using `PATINDEX`? – Cyberpau Sep 16 '16 at 03:27

John Cappelletti · Accepted Answer · 2016-09-16T04:26:21.720

2

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))

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

edited Sep 16 '16 at 04:26

answered Sep 16 '16 at 03:38

John Cappelletti

79,615
7
44
66

Thanks! I tried it and it seems to work fine but I need to get all the IDs in table2.id. It is like 3000+ IDs in there. In your code, we declare the IDs in here: `('12345,67890,10000,20000,30000',',')`. Sorry for the poor english. – Cyberpau Sep 16 '16 at 03:54
Do you have a table or query which generates the 3000+ ids? – John Cappelletti Sep 16 '16 at 03:56
@Cyberpau Sorry, I just saw the list was in table 2... one moment – John Cappelletti Sep 16 '16 at 03:58
Yes. I have a "IDs to delete" table with 3000+ ids. I want to delete all the rows from table1 with ids from the "IDs to delete table" – Cyberpau Sep 16 '16 at 04:02
It returns no result. I tried to cast it to nvarchar because my column is in nvarchar but still no result. – Cyberpau Sep 16 '16 at 04:19
@Cyberpau see my EDIT – John Cappelletti Sep 16 '16 at 04:27
I've tried your code and it really works! but I don't know why does it returns zero on my tables. I did exactly what I needed to do but still no results. – Cyberpau Sep 16 '16 at 04:40
@Cyberpau try this CharIndex(SomeField,ID)>0 it will be less precise – John Cappelletti Sep 16 '16 at 04:44
@Cyberpau what is the field name in table2? – John Cappelletti Sep 16 '16 at 04:45
When I copy a value of table2.ID then paste it, it gives 1234. But when I copy other column it doesn't give s or white spaces. I think that affects the query. The field name in table2 is ID. Ive already tried removing >< and still no results. – Cyberpau Sep 16 '16 at 04:46
@Cyberpau sounds like table2 needs to be cleaned up. Update Table2 Set ID = ltrim(rtrim(Replace(Replace(ID,char(9),''),' ',''))) – John Cappelletti Sep 16 '16 at 04:55
It works now. I used `Select * From @Table1 A Join @Table2 B on CharIndex('id>'+RTRIM(LTRIM(ID))+'<',ID)>0` – Cyberpau Sep 16 '16 at 04:55
@Cyberpau Well done. That was harder than it should have been :) cheers – John Cappelletti Sep 16 '16 at 04:56
Thank you so much! – Cyberpau Sep 16 '16 at 04:57

PATINDEX all values of a column

2 Answers2