How to compare two columns.

• tripri

  SSCommitted

  Points: 1584

  More actions

  November 8, 2010 at 10:50 am

  #388188

  Hi,

  I got two tables and i need to join them based on some results

  That is i need to compare a part of string in cola of table 1 with colx in table2 like

  Table 1

  col A

  123ac45

  45ad13

  ac

  &34ag

  Tabl2

  Col X

  ad

  ac

  ag

  So now col A in table 1 has 'ac' in 1st row of 123ac45

  so i should check that with col Z of table 2 in join condition

  So now i can write

  select * from tbl1 inner join tab2

  on (Partofstring in col A of tabl1)=tabl2.colx

  Thanq so much...

• Derrick Smith

  SSChampion

  Points: 10833

  More actions

  November 8, 2010 at 10:56 am

  #1247780

  Declare @Table1 TABLE ( A nvarchar(10))

  DECLARE @Table2 TABLE ( X nvarchar(10))

  INSERT INTO @Table1

  SELECT '123ac45' UNION ALL

  SELECT '45ad13' UNION ALL

  SELECT 'ac' UNION ALL

  SELECT '&34ag'

  INSERT INTO @Table2

  SELECT 'ad' UNION ALL

  SELECT 'ac' UNION ALL

  SELECT 'ag'

  SELECT T1.A,T2.X FROM @Table1 T1

  INNER JOIN @Table2 T2

  ON T1.A LIKE '%'+T2.x+'%'

  ORDER BY T1.A ASC

  Or vice versa..but the LIKE statement is the part you're looking for I believe.