STRING_SPLIT Function - SQL Server to PostgreSQL Migration

In SQL Server, the STRING_SPLIT is a table-valued function that splits a string into a set of rows based on the specified separator.

In PostgreSQL, you can use the STRING_TO_TABLE, or UNNEST and STRING_TO_ARRAY functions.

SQL Server:

  -- Split string using comma (,) as separator
  SELECT * FROM STRING_SPLIT('a,b,c', ',');

PostgreSQL:

  -- Split string to table using comma (,) as separator
  SELECT * FROM STRING_TO_TABLE('a,b,c', ',') AS value;
 
  -- Split string to array using comma (,) as separator and then convert to rows
  SELECT * FROM UNNEST(STRING_TO_ARRAY('a,b,c', ',')) AS value;

All queries return the following result:

value

a

b

c

If the optional 3rd parameter of the STRING_SPLIT function is not 0, SQL Server additionally outputs ordinal column, 1-based index of each substring's position in the input string:

SQL Server:

  -- Get ordinal column
  SELECT * FROM STRING_SPLIT('a,b,c', ',', 1);

Result:

value	ordinal
a	1
b	2
c	3

In PostgreSQL, you can use WITH ORDINALITY clause as follows:

PostgreSQL:

  -- Get ordinal column
  SELECT * FROM STRING_TO_TABLE('a,b,c', ',') WITH ORDINALITY AS t(value, ordinal);
 
  -- Get ordinal column
  SELECT * FROM UNNEST(STRING_TO_ARRAY('a,b,c', ',')) WITH ORDINALITY AS t(value, ordinal);

Result:

value	ordinal
a	1
b	2
c	3