Question 253633 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – how can i convert a string in a column to an array and then query a specific position in that arrray?

enter image description here

So i want to convert this string to an array using string_to_array and then query for the 3rd comma value.

I had something in mind like this

SELECT * FROM table1 WHERE string_to_array(data1, ',')[2] = '2'

But this does not seem to work. Does anybody have a suggestion?

Tags:

3

Answers


  1. 0

    Your query is not working because the array elements are 1-based in PostgreSQL, not 0-based like in many other programming languages.

    Try this code, I changed your query to look for rows where the string_to_array array’s third member equals ‘2’:

    SELECT *
FROM table1
WHERE (string_to_array(data1, ','))[3] = '2';

    Hope it works 🙂

    Login or Signup to reply.
  2. 0

    Array indexing in Postgres starts from 1 and not 0, so to get the third element you have to use index 3, try modifying your query;

    SELECT * FROM table1 WHERE string_to_array(data1, ',')[3] = '2';
    Login or Signup to reply.
  3. 0

    To get the third element :

    SELECT *, elements[3] as third_element
FROM table1
  CROSS JOIN string_to_array(data1, ',') as elements

    To query on the third element :

    SELECT *
FROM table1
  CROSS JOIN string_to_array(data1, ',') as elements
where elements[3]::varchar = '2'

    Note : first element is at position 1 (not 0)

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search