-1

I have 2 tables with a similar column called "Re", but the "Re" column in Table 1 is a list as shown below.

I want to search if Table2.Re is in Table1.Re and if so return Table1.FiN.

Table 1 

 FiN       Re
  A     ,1,2,3,4
  B     ,6,7,8,9
  C     ,3,5,6,7
  D     ,7,10,11,24

Table 2 

Re   
3
5
6
9
DasVisual
  • 779
  • 2
  • 7
  • 9
  • 1
    https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad – sticky bit Dec 28 '18 at 19:33
  • I appreciate the link. I did not create the tables, I just have to query the data from them. – DasVisual Dec 28 '18 at 19:36
  • To elaborate on @stickybit suggestion of not using comma delimited lists, the method he provided below is one of two ways. The other involves a split function. Both aren't fun and while one performs better than the other, it takes more work too. – S3S Dec 28 '18 at 19:46

2 Answers2

2

You could try with LIKE.

SELECT t1.[FiN]
       FROM [Table 2] t2
            INNER JOIN [Table 1] t1
                       ON t1.[Re] LIKE concat('%,', t2.[Re], ',%');

But seriously, you just should not use comma delimited lists.

sticky bit
  • 36,626
  • 12
  • 31
  • 42
0

Just like we use INSTR(..) for checking in an actual string if the substring exists same in this case CHARINDEX(..) will do the same if it matches then the value would be greater than 0. So, you get the desired output.

 SELECT  t2.RE FROM TABLE2 t2
 JOIN TABLE1 t1
 ON
 CHARINDEX(t1.re, t2.re)>0;

or Maks use of like as

     SELECT  t2.RE FROM TABLE2 t2
      JOIN TABLE1 t1
      ON
      t1.re LIKE "%,"+ t2.re + "%,";
Himanshu
  • 3,830
  • 2
  • 10
  • 29