Select substring from a varchar and convert to Integer array

Question

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!

Community · Accepted Answer · 2017-05-23 10:32:17Z

1

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

edited May 23, 2017 at 10:32

CommunityBot

11 silver badge

answered Dec 9, 2013 at 18:32

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ricardocaldeira Over a year ago

Your solution is so elegant! But after that I need to iterate every element from each array to pass that number to another procedure like in the first array I will call my proc 9 times: my_proc(1); my_proc(2); my_proc(3); and keep going...

Erwin Brandstetter Over a year ago

@ricardocaldeira: You can still do that after converting your data. I suppose a nested for loop. I added a link for looping arrays in modern and ancient Postgres ..

ricardocaldeira Over a year ago

Man I was trying but I don't know how to get this... If you can help me please responde the other question stackoverflow.com/questions/20480035/…

Collectives™ on Stack Overflow

Select substring from a varchar and convert to Integer array

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related