SubString

[WarkoisLives]

Hi All

Drawing a blank on this Friday afternoon.

I need to be able to see if a certain record is in another table.

Table 1
ID Product
10 ABC
20 DEF
30 GHI
40 JKL

Table 2
ID
1,3,4,5,10
10,100,200,300
10,25,500
5,6,7,40


I want it to return to see if any of the records in table 1 are in table 2 but the column in Table 2 is a string. I just need the actual logic. I know how to convert an int to a char.
Results
10 ABC
20 DEF
40 JKL

 

[gmmastros]

Based on your sample data, I think your expected results are wrong (unless I don't understand). In your expected results, you have 20,DEF but 20 does not appear in table 2.

Anyway... I suggest you try this...

Select Distinct Table1.* 
From   Table1
       Inner Join Table2
         On ',' + Table2.id + ',' like '%,' + Convert(VarChar(10), Table1.id) + ',%'

With large datasets, this code will not run very fast, but with an un-normalized table like you have with Table2, you shouldn't expect too much.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom