find the position of character in a string | Member | | Join Date: Mar 2007
Posts: 117
| | |
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.
| | Moderator | | Join Date: Dec 2006 Location: Europe
Posts: 292
| | | re: find the position of character in a string
Quote:
Originally Posted by 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. 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
| | Member | | Join Date: Mar 2007
Posts: 117
| | | re: find the position of character in a string
Quote:
Originally Posted by rski 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
| | Moderator | | Join Date: Dec 2006 Location: Europe
Posts: 292
| | | re: find the position of character in a string
>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)
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.
| | Moderator | | Join Date: Dec 2006 Location: Europe
Posts: 292
| | | re: find the position of character in a string
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)
-
|  |
Similar PostgreSQL Database bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,419 network members.
|
