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

Dragging in steps

c# winforms mouseevent drag

I have a panel divided into 10 equal rows. Then I have markers (squares with the height == height of a row) which I want to be able to drag up and down, but a markers need to be exactly fitting int...

How to fix heading at left of buttons

html css

I am developing an page but i am not really good in web designing.