Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 68F8B64FC45 for ; Mon, 8 Dec 2008 03:14:26 -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 77649-01 for ; Mon, 8 Dec 2008 03:14:23 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from fg-out-1718.google.com (fg-out-1718.google.com [72.14.220.158]) by mail.postgresql.org (Postfix) with ESMTP id 9200F64FC2F for ; Mon, 8 Dec 2008 03:14:23 -0400 (AST) Received: by fg-out-1718.google.com with SMTP id 16so828483fgg.23 for ; Sun, 07 Dec 2008 23:14:22 -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 :content-transfer-encoding:content-disposition:references; bh=PlE9OYjPwkJzk7URGh9RN00yb8wv1M14yHFc06x4Bwk=; b=mHTp0OPNHMWP0P7To9ngJWanz1vL0Z74gQNGY72BlOFlVSf69qwpyDy7cKIEz9EHY7 o9459i3zThUN3pCNWu8jXCwpaPaOh2iqNjz7gXK93qKejnZJgX5PXR+0wy7OTLbOFHGu QBAaZOd2qC0bKQgslgW/5tV5UZTt14AciEPLw= 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:content-transfer-encoding:content-disposition :references; b=ld9cj7xfgcO0qZJbSgZ9UWO2ei+tGbnsKup9E1m3rtCwt/tc+dgTSV1k1INN7IxPSA 7shlDFwrA8sZ0dvA1AXWGTtZnDCthoIbTScKfX9NhpKLmP/rtJECrkpgRe+29EuyWoeR efS0BWlRIerRWW0TfHoqUkLjZozAg9wWgXcjY= Received: by 10.223.106.200 with SMTP id y8mr1271111fao.24.1228720462269; Sun, 07 Dec 2008 23:14:22 -0800 (PST) Received: by 10.223.118.195 with HTTP; Sun, 7 Dec 2008 23:14:22 -0800 (PST) Message-ID: Date: Mon, 8 Dec 2008 08:14:22 +0100 From: "Pawel Socha" To: ss_postgres Subject: Re: How to find the position of a characted in a string Cc: pgsql-admin@postgresql.org In-Reply-To: <6c1264780812072230v6d89cbf4hf618c035d98319b4@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <6c1264780812072230v6d89cbf4hf618c035d98319b4@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200812/42 X-Sequence-Number: 30875 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