Join 2 tables on string up to space

• Paul McCurdy

  Ten Centuries

  Points: 1377

  More actions

  May 25, 2007 at 12:20 pm

  #119097

  Does anyone have an easy way to do this:

  Table 1 has string 'FENWAY CT' while Table 2 has just 'FENWAY' or 'FENWAY COURT'. I want to join the 2 tables on just the string up to the space i.e. tbl1 'FENWAY' = tbl2 'FENWAY'

  I think CHARINDEX may be a start but can't figure out how I'd use it in a 20K record table join

  Thanks!

   

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  May 25, 2007 at 12:32 pm

  #709067

  substring(tbl1.field1, 1, patindex('% %',tbl1.field1) - 1) = substring(tbl2.field1, 1, patindex('% %',tbl2.field1) -1)

   

• Paul McCurdy

  Ten Centuries

  Points: 1377

  More actions

  May 29, 2007 at 10:42 am

  #709424

  Thanks Lynn - worked beautifully!