SQL Join table-valued function with table where table field is a function input

Question

I have a table-valued function called fn_SplitCommaSep, which comma-separates a text field (from 'a, b, c' to 3 rows: a b c)

How can I join this to a table, taking a table column as an input?

For the purpose of this, say the table MyTable has 2 columns, Id and TextWithCommas, and that table-valued function fn_SplitCommaSep produces one column called TextWithoutComma

eg. something like one of these

select fs.TextWithoutComma
  from fn_SplitCommaSep(select mt.TextWithCommas from MyTable) fs 

or

select fs.TextWithoutComma, mt.Id
  from MyTable mt
    inner join fn_SplitCommaSep(mt.TextWithCommas) fs on (something)

Answer 1

Storing comma-separated values in a DB aside, take a look at APPLY

So something like:

SELECT fs.TextWithoutComma, mt.Id 
FROM   MyTable mt 
    CROSS APPLY fn_SplitCommaSep(mt.TextWithCommas) AS fs