July 14, 2011 at 5:43 am
Hi all
Looking for some advice please.
Have entries in table A that appear at any position in a string in table B but the two tables are unrelated. Is there a quick function I can utilise in 2008 that will help me search the string and get my result set?
In the scenario below I want to return the full record for table b 'hjkjhaaannn'
table a table b
aaa hjkjhaaannn
July 14, 2011 at 5:50 am
SELECT B.* FROM dbo.tableB B INNER JOIN dbo.tableA A ON B.Field LIKE '%' + A.Field + '%'
Now keep in mind that this can be slow as hell on if either table gets fairly large.
July 14, 2011 at 6:06 am
Same as above, but might be a bit faster:
select a.*
from a
join b
on CHARINDEX(a.field_1, b.field_2) >0
July 14, 2011 at 6:23 am
Zeev Kazhdan (7/14/2011)
Same as above, but might be a bit faster:select a.*
from a
join b
on CHARINDEX(a.field_1, b.field_2) >0
Why??? Same logic different function.
Got some tested code to prove your theory?
July 14, 2011 at 6:30 am
Actually it seems that I am wrong - Like should be faster than CHARINDEX:
--Quote -----
LIKE and CHARINDEX are created with two different purposes.
LIKE can use an index if the wild card pattern is only at the end. "CustomerName LIKE 'Jac%'" can use an index if one exists on CustomerName. However, "CustomerName LIKE '%Jac'" cannot use the index on CustomerName.
--- End of Quote ---
July 14, 2011 at 6:35 am
My guess is that they should be exactly the same in this case where the search is anywhere in the string. Don't have time to put up a test tho.
July 14, 2011 at 6:38 am
Thanks for this I was going to write a cursor, sometimes things are staring you in the face.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply