By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,983 Members | 1,717 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,983 IT Pros & Developers. It's quick & easy.

find the position of character in a string

100+
P: 138
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.
Jun 1 '08 #1
Share this Question
Share on Google+
4 Replies


Expert 100+
P: 700
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

Expand|Select|Wrap|Line Numbers
  1. >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;
  2.  count
  3. -------
  4.      1
  5.     11
  6. (2 rows)
  7.  
In this query i assume that there are no '.' character in the string. If there is '.' you must use another character in here
Expand|Select|Wrap|Line Numbers
  1. select case when (string_to_array(trim(trailing '.' from regexp_replace('abcdefedcba','(.)','\\1.','g')),'.')
  2.  
Another solution is to write a function doing that. Should be simple to do. If you want I can write it here
Jun 1 '08 #2

100+
P: 138
Maybe like that

Expand|Select|Wrap|Line Numbers
  1. >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;
  2.  count
  3. -------
  4.      1
  5.     11
  6. (2 rows)
  7.  
In this query i assume that there are no '.' character in the string. If there is '.' you must use another character in here
Expand|Select|Wrap|Line Numbers
  1. select case when (string_to_array(trim(trailing '.' from regexp_replace('abcdefedcba','(.)','\\1.','g')),'.')
  2.  
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
Jun 2 '08 #3

Expert 100+
P: 700
>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.
Jun 2 '08 #4

Expert 100+
P: 700
The function definition may look like that
Expand|Select|Wrap|Line Numbers
  1. create or replace function pss(text,text) returns setof integer as
  2. $rski$
  3. declare
  4. s int;
  5. begin
  6. for i in 0..length($1) loop
  7.         if substr($1,i,1)=$2 then
  8.                 return next i;
  9.         end if;
  10. end loop;
  11. end;
  12. $rski$
  13. language plpgsql
  14.  
and you can use it in that way
Expand|Select|Wrap|Line Numbers
  1. >select * from pss('alabama','a');
  2.  pss
  3. -----
  4.    1
  5.    3
  6.    5
  7.    7
  8. (4 rows)
  9.  
Jun 2 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.