String_To_Array function in PostgreSQL

Purpose: This functions splits a string to an array provided the delimeter is supplied. It returns an array.

Syntax:String_To_Array(String,delimeter)

Simple Example

Select String_To_Array('Hello World Today I am learning String_To_Array It is very cool',' ')

/* Result */
string_to_array
----------------
{Hello,World,Today,I,am,learning,String_To_Array,It,is,very,cool}

We can figure out that we are splitting the string by blank space which is our delimeter here and the function yielded an array.Every element of the array are demarcated by comma(,).

We can obtain a table of rows if we apply the UnNest function to the output of String_To_Array function

Select UNNEST(String_To_Array('Hello World Today I am learning String_To_Array It is very cool',' '))

/*Result*/
unnest
--------
"Hello"
"World"
"Today"
"I"
"am"
"learning"
"String_To_Array"
"It"
"is"
"very"
"cool"

Example 2: Using String_To_Array on a column

Let us create the environment

CREATE TABLE tblTest( ID Serial NOT NULL, Records TEXT);
INSERT INTO tblTest(Records) VALUES('Hello,World');
INSERT INTO tblTest(Records) VALUES('Today,I,am ,learning, String_To_Array function'); 

SELECT * FROM tblTest;

/*Result*/
"id";"records"
1;"Hello,World"
2;"Today,I,am ,learning, String_To_Array function"

The objective is to split the Records column data. Let us see how

Select 
	ID
	,UnNest(String_To_Array(Records,',')) AS "Splitted Record"
From tblTest;

/* Result*/
id Splitted Record
1  Hello
1  World
2  Today
2  I
2  am 
2  learning
2  String_To_Array function

We can easily write a split function to do this in SQL Server

Hope this helps.

Thanks for reading