Can anyone please help me with a query to return a single value from a list of comma separated values when used in inner join. I can paste the example query here.

select * 
from Test.dbo.Test1 
   INNER JOIN  @TestTable T ON T.Name = ???? 

The table given is Test.dbo.Test1 .I had inserted the values to a table variable @TestTable and doing an inner join. The table is:-

Id  Name
1   John,Mili,Anil,Abhi
2   John,Abhi
3   John
4   Mili,Anil,Abhi
5   Anil
6   John,Mili,Anil,Abhi

I need a query in '???' space where I can select a single value from comma separated values in 'name' field and compare with the other.

  • Which DBMS are you using?
    – user1822
    Commented Jun 2, 2016 at 6:14

1 Answer 1


mdbo and @ are hints it's MS Sql Server. Use a splitter table valued function of choice, Jeff Moden's one http://www.sqlservercentral.com/articles/Tally+Table/72993/ performs very good. So

select * 
from Test.dbo.Test1 a
   INNER JOIN  @TestTable T on T.Name in (select item from [DelimitedSplit8K](a.Name, ','))

But may I say the best way to solve the problem is to have no such a problem. Any chance Test.dbo.Test1 could be refactored to have a separate row for every item from Name column? Then take this opportunity.

it's possible to use plain like, but note it prevents optimizer from using any index. In the above version at least index on T.Name can be used.

select * 
    from Test.dbo.Test1 a
       INNER JOIN  @TestTable T on ','+a.Name+',' like '%,'+T.name+',%'
  • Can we use CHARINDEX and solve the above.? I mean without using a function, just by using a simple select query.
    – leeg4625
    Commented Jun 2, 2016 at 9:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.