How get all matching positions in a string?

Question

I have a column flag_acumu in a table in PostgreSQL with values like:

'SSNSSNNNNNNNNNNNNNNNNNNNNNNNNNNNNSNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN'

I need to show all positions with an 'S'. With this code, I only get the first such position, but not the later ones.

SELECT codn_conce, flag_acumu, position('S' IN flag_acumu) AS the_pos 
FROM dh12 
WHERE position('S' IN flag_acumu) != 0 
ORDER BY the_pos ASC;

How to get all of them?

Edit your question and provide sample data and desired results. You have essentially said: "I am using the `position()` function as documented; it is returning the correct results, and I don't like them." — Gordon Linoff, Dec 28 '15 at 15:14
This might be helpful: https://bytes.com/topic/postgresql/answers/798130-find-position-character-string — jpw, Dec 28 '15 at 15:16
Please be more explicit about what your issue is. Please describe precisely what output you are expecting, which would most certainly require you to provide sample data and sample output. — jcaron, Dec 28 '15 at 15:18

Erwin Brandstetter · Answer 1 · 2019-06-12T20:06:45.703

In Postgres 9.4 or later you can conveniently use unnest() in combination with WITH ORDINALITY:

SELECT *
FROM   dh12 d
JOIN   unnest(string_to_array(d.flag_acumu, NULL))
          WITH ORDINALITY u(elem, the_pos) ON u.elem = 'S'
WHERE  d.flag_acumu LIKE '%S%'  -- optional, see below
ORDER  BY d.codn_conce, u.the_pos;

This returns one row per match. WHERE d.flag_acumu LIKE '%S%' is optional to quickly eliminate source rows without any matches. Pays if there are more than a few such rows.

Detailed explanation and alternatives for older versions:

PostgreSQL unnest() with element number

score 2 · Answer 2 · answered Dec 28 '15 at 15:40

Since you didn't specify your needs to a point in which one could answer properly, I'm going with my assumption that you want a list of positions of occurence of a substring (can be more than 1 character long).

Here's the function to do that using:

FOR .. LOOP control structure,
function substr(text, int, int).

CREATE OR REPLACE FUNCTION get_all_positions_of_substring(text, text)
RETURNS text
STABLE
STRICT
LANGUAGE plpgsql
AS $$
DECLARE
  output_text TEXT := '';
BEGIN

FOR i IN 1..length($1)
LOOP
  IF substr($1, i, length($2)) = $2 THEN
    output_text := CONCAT(output_text, ';', i);
  END IF;
END LOOP;

-- Remove first semicolon
output_text := substr(output_text, 2, length(output_text));

RETURN output_text;
END;
$$;

Sample call and output

postgres=# select * from get_all_positions_of_substring('soklesocmxsoso','so');
 get_all_positions_of_substring
--------------------------------
 1;6;11;13

martin81930 martin81930 · Answer 3 · 2016-01-23T14:50:06.927

This works too. And a bit faster I think.

create or replace function findAllposition(_pat varchar, _tar varchar) 
returns int[] as
$body$
declare _poslist int[]; _pos int;
begin

_pos := position(_pat in _tar);
while (_pos>0)
loop

    if array_length(_poslist,1) is null then
        _poslist := _poslist || (_pos);
    else
        _poslist := _poslist || (_pos + _poslist[array_length(_poslist,1)] + 1);
    end if;

    _tar := substr(_tar, _pos + 1, length(_tar));
    _pos := position(_pat in _tar);

end loop;
return _poslist;

end;
$body$
language plpgsql;

Will return a position list which is an int array.

{position1, position2, position3, etc.}

How get all matching positions in a string?

3 Answers3

Linked