Select substring from a varchar and convert to Integer array

I have a VARCHAR of numbers inside my stored procedure, these numbers are organized as arrays, I will show an example below:

{1,2,3,4,5,6,7,8,9},{1,2,3,4,5},{1,2,3},{9} -- This is a STRING

I want to do a FOR loop to select every time a substring from this set between {} and convert this to an array of integers.

So at first time inside my loop I will have:

{1,2,3,4,5,6,7,8,9}

So I will use array_to_string to convert this to an integer[]

At second time I will have:

{1,2,3,4,5}

and keep going using array_to_string

Any tips? Careful, because unfortunately I'm using PostgreSQL 8.3!

Answers


You could do it in a single statement:

SELECT string_to_array(unnest(string_to_array(
          trim('{1,2,3,4,5,6,7,8,9},{1,2,3,4,5},{1,2,3},{9}', '{}')
          , '},{')), ',')::int[]

.. in Postgres 8.4 or later. 8.3 has reached EOL. Urgently consider an upgrade.

However, there is regexp_split_to_table() in 8.3 already:

SELECT string_to_array(regexp_split_to_table(
          trim('{1,2,3,4,5,6,7,8,9},{1,2,3,4,5},{1,2,3},{9}', '{}')
          , '},{'), ',')::int[]

-> SQLfiddle demo for Postgres 8.3.

For looping the array, consider this related answer: Postgres - array for loop


Need Your Help

How do I fix this syntax error? I am confused

python syntax-error

Here is all my coding that I did but I keep getting this syntax error. It will be explained more at the bottom.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.