Post reply

IN with LIKE?

  • February 15, 2011 at 2:36 pm

    #245656

    I have table_A which has the following data:

    ID StkNumber

    ------ ----------

    329577abc61642

    424084067327864

    433340436135

    433812099428198

    433813005408415

    465324255963971

    465325114180942

    47538915126473

    47539089810499

    475391904920080

    475392931976760

    47539370348016

    47539415698979

    475395d73c2006784

    4753962030005694

    4753972130000149

    4753982130000235

    47539933324503

    4754012130000648

    4754022130000586

    475403835835e12

    481404379719218

    595827abc1253420

    623885ABC2454385

    623886ABC41706

    669989abc851077

    669990ABC99999

    670896913012712

    672120

    675743913012800

    67883418821-0049305

    And table_B with the following data:

    ID StkNumber

    ----- --------------------

    7463 00000000000913922004

    7464 00000000000904920080

    7465 000000005029-0028915

    7466 00000000000015126473

    7467 0000000000ABC9810499

    7468 000000011665-0039892

    7469 000000014624-0036171

    7470 000000005921-0023396

    7471 00000000000913016153

    7472 00000000000070348016

    7473 00000000000904920080

    7474 000000000d73c2006784

    7475 00000000000913922004

    7476 000000005029-0028915

    7477 00000000000015126473

    7478 00000000000089810499

    7479 000000011665-0039892

    7480 000000014624-0036171

    7481 000000005921-0023396

    7482 0000000000abc7348016

    7483 00000000000913016153

    My question is, can I use some combination of LIKE/IN syntax to match the StkNumber?

    TIA,

    KK

    (This seems really simple to me, but I'm feeling under the weather today, and my head is a bit foggy. Any help/suggestions are greatly appreciated!)

  • February 15, 2011 at 2:45 pm

    #1287330

    You could use

    SELECT *

    FROM table_A A

    INNER JOIN table_B B

    ON B.StkNumber LIKE '%'+A.StkNumber

    However, this will cause a table scan since there is no index the query can use.

    An alternative might be adding a computed persisted (indexed) column that would bring the StkNumber values from table_A in the equivalent format like table_B.

    Maybe using something like

    SELECT REPLICATE('0', 20-LEN(A.StkNumber)) + A.StkNumber


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • February 15, 2011 at 3:00 pm

    #1287336

    Thanks Lutz!

    See, I knew it was simple!

    Many Thanks!

    KK

  • February 15, 2011 at 3:05 pm

    #1287338

    krushkoder (2/15/2011)

    Thanks Lutz!

    See, I knew it was simple!

    Many Thanks!

    KK

    Please read my reply completely.

    The query I posted may perform really bad. Hence my advice to add the computed column.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • February 15, 2011 at 3:21 pm

    #1287343

    Yes, I tried the first one and due to the possibility of a blank StkNumber in a row, the cartesian result was probably going to be 4-5 million rows at least! : ) So, using a modified version of your second snippet, I was able to make it work as desired. You definitely got me around my flu-induced (or at the very least "assisted") mental block, and pointed in the right direction.

    Many happy RETURNs (pun intended...)

    KK

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply