find the position of character in a string

[coolminded]

hi all,

how could i find the corresponding positions on a string which have two or more same characters
i mean
select position('a' in 'abcdefedcba')
it should return 1 and 11 simultaneoulsy

but above query returns 1 only. how could i get the next position value.

[rski]

hi all,

how could i find the corresponding positions on a string which have two or more same characters
i mean
select position('a' in 'abcdefedcba')
it should return 1 and 11 simultaneoulsy

but above query returns 1 only. how could i get the next position value.

Maybe like that

>select * from (select case when (string_to_array(trim(trailing '.' from regexp_replace('abcdefedcba','(.)','\\1.','g')),'.'))[i]='a' then i end as count from generate_series(1,length('abcdefedcba')) i) foo where count is not null;
 count
-------
     1
    11
(2 rows)

In this query i assume that there are no '.' character in the string. If there is '.' you must use another character in here

select case when (string_to_array(trim(trailing '.' from regexp_replace('abcdefedcba','(.)','\\1.','g')),'.')

Another solution is to write a function doing that. Should be simple to do. If you want I can write it here

[coolminded]

Maybe like that

>select * from (select case when (string_to_array(trim(trailing '.' from regexp_replace('abcdefedcba','(.)','\\1.','g')),'.'))[i]='a' then i end as count from generate_series(1,length('abcdefedcba')) i) foo where count is not null;
 count
-------
     1
    11
(2 rows)

In this query i assume that there are no '.' character in the string. If there is '.' you must use another character in here

select case when (string_to_array(trim(trailing '.' from regexp_replace('abcdefedcba','(.)','\\1.','g')),'.')

Another solution is to write a function doing that. Should be simple to do. If you want I can write it here

hi,
thanx for the reply,
but it didn't work in my PgAdmin. It shows nothing. i'm using PostgreSQL 8.1.3. does it depend on the version too??

and it will be very kind if you write a code or a function for doing that. i'll be very thankful to you.
coolminded

[rski]

>select * from (select case when (string_to_arra y(trim(trailing '.' from regexp_replace( 'abcdefedcba',' (.)','\\1.','g' )),'.'))[i]='a' then i end as count from generate_series (1,length('abcd efedcba')) i) foo where count is not null;
count
-------
1
11
(2 rows)

sorry my mistake a put a space after '.' now it should work, i'll send you a function just when i find some free time to write it.

[rski]

The function definition may look like that

create or replace function pss(text,text) returns setof integer as
$rski$
declare
s int;
begin
for i in 0..length($1) loop
        if substr($1,i,1)=$2 then
                return next i;
        end if;
end loop;
end;
$rski$
language plpgsql

and you can use it in that way

>select * from pss('alabama','a');
 pss
-----
   1
   3
   5
   7
(4 rows)