Searching for a string in two unrelated tables

  • 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

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

  • Same as above, but might be a bit faster:

    select a.*

    from a

    join b

    on CHARINDEX(a.field_1, b.field_2) >0

  • 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?

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

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

  • 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