SQL Inner join with function returning table

Question

I wonder if the following is possible.

I have a SQL function that splits a coma separated string returning a table with the splited values.

hello,my name,is,John

returns a table:

ITEM
-----
hello
my name
is
John

A have a table storing, for each row, an ID and a coma separated string:

VALUE_TO_SPLIT
--------------
1 | a,b,c
1 | d,e,f,g
2 | a,b
3 | a
3 | b,c,f

Without using cursors, is it possible to obtain the resulting table?

RESULTING_TABLE
1 | a
1 | b
1 | c
1 | d
1 | e
1 | f
1 | g
2 | a 
2 | b
3 | a  
3 | b  
3 | c  
3 | f  

That is, a rows for each splited value with its id.

Solution

The function looks like an inline table-valued function; if you can include the function we can also suggest whether it is an efficient implementation (not all are).

SELECT t.col, s.Item
FROM dbo.tablename AS t
CROSS APPLY dbo.function_name(t.value) AS s;

You should also consider not storing separate values as comma-separated lists in the first place. A database table should not be a JSON container, IMHO.

Comments

[nachovall]

That's exactly what I want. Thanks. About storing coma-separated lists in the database, totally agree, but it is inherited from previous developers :)