Re: [HACKERS] string_to_array with empty input

On Thu, Apr 2, 2009 at 12:10 PM, David E. Wheeler <david(at)kineticode(dot)com> wrote:
> 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;

Ah! Thanks for the tip.

...Robert