How Get All Matching Positions In A String?

I have a column flag_acumu in a table in PostgreSQL with values like: 'SSNSSNNNNNNNNNNNNNNNNNNNNNNNNNNNNSNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN' I need to show all positions wit

Solution 1:

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 IN1..length($1)
LOOPIF substr($1, i, length($2)) = $2THEN
    output_text := CONCAT(output_text, ';', i);ENDIF;
ENDLOOP;

-- 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

Solution 2:

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

SELECT*FROM   dh12 d
JOINunnest(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 belowORDERBY 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

Solution 3:

This works too. And a bit faster I think.

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

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

    if array_length(_poslist,1) isnullthen
        _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.}