pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to find the position of a characted in a string
4+ messages / 2 participants
[nested] [flat]
* How to find the position of a characted in a string
@ 2008-12-08 06:30 ss_postgres <ss.postgres@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: ss_postgres @ 2008-12-08 06:30 UTC (permalink / raw)
To: pgsql-admin
Hi,
I am trying to find the position of a character in a string which appears
multiple times.
The POSITION function returns the position of the character when it first
occurs.
For eg:
select position ( ' ' in 'Hi How are you');
This returns the result as "3".
But i need to result to be
3
7
11
Can anyone please let me know what would be the best way to do this.
Thanks
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to find the position of a characted in a string
@ 2008-12-08 07:14 Pawel Socha <pawel.socha@gmail.com>
parent: ss_postgres <ss.postgres@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Pawel Socha @ 2008-12-08 07:14 UTC (permalink / raw)
To: ss_postgres <ss.postgres@gmail.com>; +Cc: pgsql-admin
2008/12/8 ss_postgres <ss.postgres@gmail.com>:
> Hi,
>
> I am trying to find the position of a character in a string which appears
> multiple times.
>
> The POSITION function returns the position of the character when it first
> occurs.
>
> For eg:
>
> select position ( ' ' in 'Hi How are you');
>
> This returns the result as "3".
>
>
> But i need to result to be
> 3
> 7
> 11
>
>
> Can anyone please let me know what would be the best way to do this.
>
> Thanks
>
My first things:
merlin=> select distinct(x.case) from (select (case when substring('Hi
How are you' from r for 1) = ' ' then r else null end) from
generate_series(0, length('Hi How are you')) as r) as x;
case
------
3
7
11
--
Pawel Socha
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to find the position of a characted in a string
@ 2008-12-08 10:34 ss_postgres <ss.postgres@gmail.com>
parent: Pawel Socha <pawel.socha@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: ss_postgres @ 2008-12-08 10:34 UTC (permalink / raw)
To: Pawel Socha <pawel.socha@gmail.com>; +Cc: pgsql-admin
On Mon, Dec 8, 2008 at 5:44 PM, Pawel Socha <pawel.socha@gmail.com> wrote:
> 2008/12/8 ss_postgres <ss.postgres@gmail.com>:
> > Hi,
> >
> > I am trying to find the position of a character in a string which appears
> > multiple times.
> >
> > The POSITION function returns the position of the character when it first
> > occurs.
> >
> > For eg:
> >
> > select position ( ' ' in 'Hi How are you');
> >
> > This returns the result as "3".
> >
> >
> > But i need to result to be
> > 3
> > 7
> > 11
> >
> >
> > Can anyone please let me know what would be the best way to do this.
> >
> > Thanks
> >
>
> My first things:
>
> merlin=> select distinct(x.case) from (select (case when substring('Hi
> How are you' from r for 1) = ' ' then r else null end) from
> generate_series(0, length('Hi How are you')) as r) as x;
> case
> ------
> 3
> 7
> 11
>
>
> --
> Pawel Socha
>
Hi Pawal,
Thanks a lot.
Suppose i need to do the same thing on a column in a table, can you please
let me know how to write the query.
Also how to eliminate the null result at the end? For eg: Running the above
query gives 4 rows, the last row with null value.
Thanks for your help.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to find the position of a characted in a string
@ 2008-12-09 18:15 Pawel Socha <pawel.socha@gmail.com>
parent: ss_postgres <ss.postgres@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Pawel Socha @ 2008-12-09 18:15 UTC (permalink / raw)
To: ss_postgres <ss.postgres@gmail.com>; +Cc: pgsql-admin
2008/12/8 ss_postgres <ss.postgres@gmail.com>:
>
>
> On Mon, Dec 8, 2008 at 5:44 PM, Pawel Socha <pawel.socha@gmail.com> wrote:
>>
>> 2008/12/8 ss_postgres <ss.postgres@gmail.com>:
>> > Hi,
>> >
>> > I am trying to find the position of a character in a string which
>> > appears
>> > multiple times.
>> >
>> > The POSITION function returns the position of the character when it
>> > first
>> > occurs.
>> >
>> > For eg:
>> >
>> > select position ( ' ' in 'Hi How are you');
>> >
>> > This returns the result as "3".
>> >
>> >
>> > But i need to result to be
>> > 3
>> > 7
>> > 11
>> >
>> >
>> > Can anyone please let me know what would be the best way to do this.
>> >
>> > Thanks
>> >
>>
>> My first things:
>>
>> merlin=> select distinct(x.case) from (select (case when substring('Hi
>> How are you' from r for 1) = ' ' then r else null end) from
>> generate_series(0, length('Hi How are you')) as r) as x;
>> case
>> ------
>> 3
>> 7
>> 11
>>
>>
>> --
>> Pawel Socha
>
>
> Hi Pawal,
>
> Thanks a lot.
>
> Suppose i need to do the same thing on a column in a table, can you please
> let me know how to write the query.
Just create function ;)
> Also how to eliminate the null result at the end? For eg: Running the above
> query gives 4 rows, the last row with null value.
select distinct(x.case) from (select (case when substring('Hi How are
you' from r for 1) = ' ' then r else null end) from generate_series(0,
length('Hi How are you')) as r) as x where x.case is not null;
> Thanks for your help.
>
>
--
Serdecznie pozdrawiam
Pawel Socha
pawel.socha@gmail.com
programista/administrator
perl -le 's**02).4^&-%2,).^9%4^!./4(%2^3,!#+7!2%^53%2&**y%& -;^[%"`-{
a%%s%%$_%ee'
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2008-12-09 18:15 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-12-08 06:30 How to find the position of a characted in a string ss_postgres <ss.postgres@gmail.com>
2008-12-08 07:14 ` Pawel Socha <pawel.socha@gmail.com>
2008-12-08 10:34 ` ss_postgres <ss.postgres@gmail.com>
2008-12-09 18:15 ` Pawel Socha <pawel.socha@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox