Can you join on text?

• Billy - a cool nerd

  Old Hand

  Points: 337

  More actions

  August 12, 2010 at 9:56 am

  #225822

  I have two tables that have nothing in common except for a text Desc column.

  I've tried casting them and substring but I get error 207 invalid column.

  Open to all suggestions.:-D

  😎
  Billy

• Lowell

  SSC Guru

  Points: 323546

  More actions

  August 12, 2010 at 10:10 am

  #1206165

  yes, it'll be a bit slow, because you'll have to convert the TEXT field to a varchar(8000);

  here's an examplei put together, note how i made a searchterm in a subquery, because you might want to find some specific string in one of the descriptions.

  SELECT T1.DESCRIP,T2.DESCRIP

  FROM T1

  LEFT OUTER JOIN

  (SELECT

  SUBSTRING(CONVERT(VARCHAR(8000),T2.DESCRIP),1,40) AS SEARCHTERM,

  DESCRIP

  FROM T2) AS T2

  --hope the first 40 characters exist in t1?

  ON CONVERT(VARCHAR(8000),T2.DESCRIP) LIKE '%' + T2.SEARCHTERM + '%'

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Michael Valentine Jones

  SSC Guru

  Points: 64818

  More actions

  August 12, 2010 at 10:37 am

  #1206192

  You could cast them to varchar(max) to do the join, or better yet, convert the columns to varchar(max).

  A way you could speed this up is to create an indexed integer column that contains a checksum of the TEXT column, and then join on the checksum column with a secondary compare on the value in the text column to eliminate duplicates.