This makes use of Moden's DelimitedSplit8k which you can download in the scripts section.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION tvf_ChildRowNames

(

@rowName varchar(250)

)

RETURNS TABLE

AS

RETURN

(

SELECT name from table2 where exists ( select 1 from [DelimitedSplit8K](name, ',') where item = @rowName)

)

GO

You use it like,

select name from tvf_ChildRowNames('canola')