Position of string-match in PostgreSQL 9.5 array

Question

I'm trying to search for the array position of a matching string, I see there is a post with the position of a character on a string but not one with an array, here is an example of what I want to achieve:

array['potato-salad','cucumber-salad','eggplant-pie','potato-soup']

I want to know where the elements containing the word 'potato' are, so the result should be something like this:

[1,4]

I was trying to obtain the lengths of all elements, then converting the array to string, and searching for the string to see where the string matches and compare if the position can go in between any of the array elements lengths, but this doesn't work if my number of elements in the array is variable, and in my problem it is.

Lukasz Szozda · Accepted Answer · 2016-04-05 12:53:22Z

If you want exact match use array_positions:

CREATE TABLE my_tab(ID INT, col VARCHAR(100)[]);

INSERT INTO my_tab(ID, col)
VALUES (1, array['potato-salad','cucumber-salad','eggplant-pie','potato-soup']),
       (2, array['potato']);

Query:

SELECT *
FROM my_tab
,LATERAL array_positions(col, 'potato-salad') AS s(potato_salad_position)
WHERE s.potato_salad_position <> '{}';

Output:

╔════╦════════════════════════════════════════════════════════╦═══════════════════════╗
║ id ║                          col                           ║ potato_salad_position ║
╠════╬════════════════════════════════════════════════════════╬═══════════════════════╣
║  1 ║ {potato-salad,cucumber-salad,eggplant-pie,potato-soup} ║ {1}                   ║
╚════╩════════════════════════════════════════════════════════╩═══════════════════════╝

If you want to use LIKE search with wildcards you could use unnest WITH ORDINALITY:

SELECT id, array_agg(rn) AS result
FROM my_tab
,LATERAL unnest(col) WITH ORDINALITY AS t(val,rn)
WHERE val LIKE '%potato%'
GROUP BY id;

Output:

╔════╦════════╗
║ id ║ result ║
╠════╬════════╣
║  1 ║ {1,4}  ║
║  2 ║ {1}    ║
╚════╩════════╝

Collectives™ on Stack Overflow

Position of string-match in PostgreSQL 9.5 array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related