Post reply

Join using one col as a Regular Expression

  • December 12, 2003 at 8:08 am

    #83099

    I have two tables. Table A has a.id (int unique), and a.somelongVarChar (varchar(50)). Table B has b.id (int unique) and b.someshorterVarchar (varchar(25)). These tables were created from flat files derived from two outside sources. Basically my requirements are quite simple. I need to create a new table with a.id, a.somelongVarChar, b.id where a.somelongVarchar Like ‘%’ + b.someshorterVarchar (this would be my regular expression) + ‘%’. You get the idea. In the end a.id should remain unique and there’s no guarantee that all a.id will be included. I would be grateful if one of you guru’s could impart your wisdom to me on this task.

    TIA

  • December 12, 2003 at 8:24 am

    #485960

    Since A.ID must remain unique, I ask whether there can exist multiple records in B for a single record in A (given your "join" criteria)? If Not , then something like ... 

     

    SELECT A.ID AS ID, a.somelongVarChar, B.ID AS BID 

    INTO C 

    From A 

    Join B on CHARINDEX(B.someshorterVarchar, A.somelongVarChar) <> 0

    If "multiple matching Bs", then you'ed want to use an Aggregate (Min(), Max()) to decide witch B record you would want.



    Once you understand the BITs, all the pieces come together

  • December 12, 2003 at 8:51 am

    #485973

    OR

    INSERT INTO C ( ID, slVarChar, BID )

    SELECT A.ID, a.somelongVarChar, B.ID

    From B

    Join A ON A.somelongVarChar LIKE '%' + B.someshorterVarchar + '%'

    Guarddata-

Viewing 3 posts - 1 through 2 (of 2 total)

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