Function that returns multiple columns

Question

I am creating a function that splits up strings. I want to pass the function to a string and get several rows back (the amount of rows would be dynamic depending on the length of the string).

I was thinking of possibly using a table function and joining this to my query.

I am NOT asking for this to be written for me, I am simply wondering if this is even possible in SQL Server 2014, and what the best approach would be if it is.

So the string 'ABC' would be returned as:

COL1  COL2  COL3
A      B    C

Answer 1

I am simply wondering if this is even possible in SQL Server 2014

Yes, you can create table-valued user defined functions that return, well, a table value.

So the string 'ABC' would be returned as:

COL1  COL2  COL3
A      B    C

Well, now you're in trouble - table-valued functions must have a return value with a pre-defined schema, so you can't dynamically set the column names. You could return those as rows, though:

Item  Value
1     A
2     B
3     C