November 4, 2011 at 5:31 pm
Hello all!
I'm afraid I need some help with T-SQL :s
I have 2 tables:
----------------------------
table1
Date |Text
----------------
5/11 | bla blo bli
4/11 | boeboe
table2
Value1 | Value2
------------------
dada | bla
dada2 | bi bi
-----------------------------
What I would like to achieve is some kind of:
"delete from table1 where Text like (select Value2 from table2)"
(which doesn't work of course)
So I want to remove the rows from table 1 , which also exist 'LIKE' Value2 in table2. (which should remove row1 - 5/11 in this example)
But I'm a bit struggling with this. Is there somebody who can help me with this?
Thanks in advance.
November 4, 2011 at 6:14 pm
Have you tried to use any joins using a charindex or patindex clause such as:
DELETE t1
FROM table1 t1
JOIN table2 t2
ON CHARINDEX(t2.value2, t1.[text], 1) >0
November 4, 2011 at 6:17 pm
One note of caution I forgot to mention in previous post is about performance. If the tables are very large, another solution may be required.
November 4, 2011 at 7:45 pm
LIKE at times make the query SARGable but CHARINDEX. CHARINDEX might cut performance form the total query ( My personal tryst with CHARINDEX has always been dreary 🙁 ) May be there are ways to expedite CHARINDEX, but i leave it to the gurus to comment on.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply