From string to integer

Question

I've a column in my postgresql database as ids with data type as character varying(2000)

The records in it are in form of

'1,2,5,8,9,10,12'
'23,34,65,99'
'11,16,86,77,75,95,100,14'

I want values as separate integers

Desired output - 1,2,5,8,9,23,34,65,.........,77

I tried things like split_part but it does not return all the part of query also the length of records are different.

According to your description here, sample input and output look the same, both are a comma-separated lists. So why must they be changed at all? Could you please provide sample input and expected outcome as tables to make your requirements more clear? I first thought you just need to put all entries together in one line which is sorted, but somehow you don't want to have the 10 and 12 from the first row? — Jonas Metzler, Nov 07 '22 at 05:39
*"I want values as separate integers"* Be more specific, please. Each row separately, or some aggregation? One array of integer per row? Or one integer per row (splitting original row into multiple rows, obviously) ... — Erwin Brandstetter, Nov 07 '22 at 05:46

score 0 · Answer 1 · answered Nov 07 '22 at 05:49

Assuming valid syntax, basically use string_to_array() to convert the string to an array (defaulting to type text[]), and cast that to int[]:

SELECT string_to_array(ids, ',')::int[] AS ids_as_array_of_int
FROM   tbl;

split_part() would be good to fetch one specific element of the comma-separated list. See:

1 Answers1