Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id D966C64FC91 for ; Mon, 8 Dec 2008 06:34:15 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 05918-04 for ; Mon, 8 Dec 2008 06:34:09 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from rv-out-0506.google.com (rv-out-0506.google.com [209.85.198.232]) by mail.postgresql.org (Postfix) with ESMTP id 6014464FC48 for ; Mon, 8 Dec 2008 06:34:06 -0400 (AST) Received: by rv-out-0506.google.com with SMTP id b25so1116359rvf.43 for ; Mon, 08 Dec 2008 02:34:04 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:cc:in-reply-to:mime-version:content-type:references; bh=qMGwThCqB7Mbu09SCfOr1+fPjlWzzNK6i0zNWRMfeww=; b=W4gKu61w9q5EbejtIvH7BQTV33zIZQZaCG0DN6gRFIsJdMsTccePh78lGN8MIP+xxy ccUpZjgWgfbnt2W25bJp+MxX6jfM19v50XB/VXOZ13d5K/sLac3aj58yv6X1uHduO5AS +MPVXTNSyzsYoU1hjgRyUR3b5bt6abUfQ2pro= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version :content-type:references; b=fd7r5hAtaSC0nLu3qlC/PaxGs4vQx1AdsjzvW1Wyv7nZlYV3GtuH5NdES09TRSTUXW OI+vXZZQ23X37xwVTNUdmjo8XJUaPFMFyFa39C2GAK7YPoAujz30J3gCNWg8qvKEr39+ 8h13jgxjreQZ83jwlP2L0LS4vpdzPwYlwJKHk= Received: by 10.140.147.5 with SMTP id u5mr966540rvd.86.1228732444565; Mon, 08 Dec 2008 02:34:04 -0800 (PST) Received: by 10.141.43.3 with HTTP; Mon, 8 Dec 2008 02:34:04 -0800 (PST) Message-ID: <6c1264780812080234k6c249d9aje780f312c8adff4@mail.gmail.com> Date: Mon, 8 Dec 2008 21:04:04 +1030 From: ss_postgres To: "Pawel Socha" Subject: Re: How to find the position of a characted in a string Cc: pgsql-admin@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_60232_17640775.1228732444534" References: <6c1264780812072230v6d89cbf4hf618c035d98319b4@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200812/43 X-Sequence-Number: 30876 ------=_Part_60232_17640775.1228732444534 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On Mon, Dec 8, 2008 at 5:44 PM, Pawel Socha wrote: > 2008/12/8 ss_postgres : > > 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. ------=_Part_60232_17640775.1228732444534 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline

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.

------=_Part_60232_17640775.1228732444534--