Re: [GENERAL] string_to_array with empty input

On Apr 1, 2009, at 12:19 PM, Robert Haas wrote:

>> my @ints = map { $_ || 0 } split ',', $string;
>>
>> This ensures that I get the proper number of records in the example
>> of something like '1,2,,4'.
>
> I can't see that there's any way to do this in SQL regardless of how
> we define this operation.

It's easy enough to write a function to do it:

CREATE OR REPLACE FUNCTION trim_blanks (anyarray) RETURNS anyarray AS $$
     SELECT ARRAY(
         SELECT CASE WHEN $1[i] IS NULL OR $1[i] = '' THEN '0' ELSE
$1[i] END
           FROM generate_series(1, array_upper($1, 1)) s(i)
          ORDER BY i
     );
$$ LANGUAGE SQL IMMUTABLE;

Best,

David