Get element of character varying type by index or convert it to array

Question

I have some sql function that returns character varying type. The output is something like this: 'TTFFFFNN'. I need to get this characters by index. How to convert character varying to array?

Answer 1

Use string_to_array() with NULL as delimiter (pg 9.1+):

SELECT string_to_array('TTFFFFNN'::text, NULL) AS arr;

Per documentation:

In string_to_array, if the delimiter parameter is NULL, each character in the input string will become a separate element in the resulting array.

In older versions (pg 9.0-), the call with NULL returned NULL. (Fiddle.)

To get the 2nd position (example):

SELECT (string_to_array('TTFFFFNN'::text, NULL))[2] AS item2;

Alternatives

For single characters I would use substring() directly, like @a_horse commented:

SELECT substring('TTFFFFNN'::text, 2, 1) AS item2;

SQL Fiddle showing both.

For strings with actual delimiters, I suggest split_part():

• Split comma separated column data into additional columns

Only use regexp_split_to_array() if you must. Regular expression processing is more expensive.

Answer 2

In addition to the solutions outlined by Erwin and a horse, you can use regexp_split_to_array() with an empty regexp instead:

select regexp_split_to_array('TTFFFFNN'::text, '');

With an index, that becomes:

select (regexp_split_to_array('TTFFFFNN'::text, ''))[2];