Обсуждение: How to find the position of a characted in a string
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
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.
11
Can anyone please let me know what would be the best way to do this.
Thanks
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
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>:My first things:> 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
>
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.
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'