Possible to create a function that returns more than one column?

Question

Does SQL have the concept of a function that returns multiple columns, outside of a table function? Here would be an example:

SELECT
    id,
    SPLIT_NAME(name, ' ')
FROM
    table

And I want the SPLIT_NAME function to produce two columns, one for first and one for last, so the output would be:

id      first        last
1        tom         jones

I know this can be done with:

SELECT id, SPLIT(...) first, SPLIT(...) last FROM table

Or possibly with a table function such as:

SELECT id, first, last FROM table, SPLIT_NAME(name, ' ')

But, wondering if SQL has any sort of scalar-ish function that can produce multiple outputs, as I think a join could be quite expensive (I think?) if joining to something like a billion rows where (hopefully) the function itself could just be inlined.

Note: either Postgres or SQL Server is fine.

Which dialect? SQL Server? MySQL? PostgreSQL? Oracle? Etc? (You probably want Table Valued Functions, even if you return just one row, this allows multiple columns. And then use of APPLY or LATERAL joins to call that function row by row. But the exact answer depends on the DBMS you're using.) — MatBailie, Nov 02 '21 at 23:44
For SQL Server you would cross apply an inline table valued function that returns a single row and multiple columns. As the name suggests this is inlined. — Martin Smith, Nov 02 '21 at 23:47
That's like saying you don't mind if it's C or Delphi. The syntax, functionality, etc, is different between DBMS, they're effectively different languages with different patterns. This is a Very DBMS specific question. — MatBailie, Nov 02 '21 at 23:47
@MatBailie just updated it. Either SQL Server or Postgres is ok. — David542, Nov 03 '21 at 00:05

MatBailie · Accepted Answer · 2021-11-03T00:25:20.250

The two concepts you need are...

inlined table valued functions (even if you only return one row, allows multiple columns)
APPLY or LATERAL JOIN to call the function for each input row

SQL Server, for example might be written as...

SELECT
  table.id,
  name_parts.first_name,
  name_parts.last_name
FROM
  table
CROSS APPLY
  SPLIT_NAME(table.name, ' ')   AS name_parts

Other dialects might use lateral joins, different functionality all together, or might not have the functionality at all.

EDIT: An example of inline table valued function...

Again, this is SQL Server specific, and does Not generalise to other DBMS...

CREATE FUNCTION [dbo].[SPLIT_NAME] (@name NVARCHAR(MAX))
RETURNS TABLE
AS
RETURN
  SELECT
    SUBSTRING(@name, 1, PATINDEX('% %',@name)            ) AS first_name,
    SUBSTRING(@name,    PATINDEX('% %',@name), LEN(@name)) AS last_name

It's inline because there is no begin/end, procedural language, etc. Its just a SELECT statement (which can have a FROM clause, but doesn't have to), and some parameters.

(I wrote that on a phone, it might not be completely syntactically correct, but demonstrates the concept.)

thanks, what's an example of an inlined table valued function? — David542, Nov 02 '21 at 23:59

Possible to create a function that returns more than one column?

1 Answers1