Let us learn Array_To_String function in PostgreSQL

Niladri.Biswas
Posted by in PostgreSQL category on for Beginner level | Points: 250 | Views : 5726 red flag

In this article we will learn Array_To_String function in PostgreSQL

Introduction

This functions concatenates an array element to a string provided the join string is supplied. It returns a string.

Syntax:

Array_To_String(Array,join string)

Straight To Experiment

Example 1

Select Array_To_String(ARRAY['Hello','World','Today','I','am','learning','Array_To_String','It','is','very','cool'], ' ')
/* Result */
array_to_string
----------------
"Hello World Today I am learning Array_To_String It is very cool"

The array elements has been concatenated by our supplied join string blank space.

Example 2: Using Array_To_String on a column

Let us create the environment

CREATE TABLE tblTest(ID Serial NOT NULL, Col1 TEXT,Col2 TEXT,Col3 TEXT NULL, Col4 TEXT  NULL, Col5 TEXT  NULL);
INSERT INTO tblTest(Col1,Col2,Col3,Col4,Col5) VALUES('Hello','World',Null,Null,Null);
INSERT INTO tblTest(Col1,Col2,Col3,Col4,Col5) VALUES('Today','I','am ','learning', 'String_To_Array function'); 

SELECT * FROM tblTest;

/*Result*/
"id";"col1";"col2";"col3";"col4";"col5"
1;"Hello";"World";"";"";""
2;"Today";"I";"am ";"learning";"String_To_Array function"

The objective is to combine the Col1,Col2,Col3,Col4,Col5 column data by the string '~~'. Let us see how

SELECT ID,
	Array_To_String(
				ARRAY[Col1,Col2,Col3,Col4,Col5],'~~'
			)
FROM tblTest;

/* Result*/
id array_to_string
1  Hello~~World
2  Today~~I~~am ~~learning~~String_To_Array function

Conclusion

So in this article we have learnt about Array_To_String function in PostgreSQL.Hope this will be useful.Thanks for reading.

Page copy protected against web site content infringement by Copyscape

About the Author

Niladri.Biswas
Full Name: Niladri Biswas
Member Level: Platinum
Member Status: Member
Member Since: 10/25/2010 11:04:24 AM
Country: India
Best Regards, Niladri Biswas
http://www.dotnetfunda.com
Technical Lead at HCL Technologies

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)