Received: from localhost (unknown [200.46.204.184])
	by developer.postgresql.org (Postfix) with ESMTP id 9BE9B2E003D
	for <pgsql-sql-postgresql.org@developer.postgresql.org>;
	Thu, 24 Apr 2008 10:34:58 -0300 (ADT)
Received: from developer.postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 53359-01
	for <pgsql-sql-postgresql.org@developer.postgresql.org>;
	Thu, 24 Apr 2008 10:34:44 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5
Received: from nf-out-0910.google.com (nf-out-0910.google.com
 [64.233.182.190])
	by developer.postgresql.org (Postfix) with ESMTP id 0DA3E2E0087
	for <pgsql-sql@postgresql.org>; Thu, 24 Apr 2008 10:34:51 -0300 (ADT)
Received: by nf-out-0910.google.com with SMTP id f5so1053868nfh.23
	for <pgsql-sql@postgresql.org>; Thu, 24 Apr 2008 06:34:50 -0700 (PDT)
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:mime-version:content-type;
	bh=VfY96MelKXFQoYo2TSfbW8vNBL9taqrwYh5broUtJA0=;
	b=oDDDHhaySoqcarfKCocQrCGRqsKj/HTqnHOZsq2W+WiLPGzzXHoesvh8ThkcMsxeWsPGC0Qnb3TeSvCa3hps3hPSQGysP+D0I+zW6/r5/9gZr4HWtoFV5Zoz1LogGGgcOUnGLDl9PW3Lv6qiTmD3tHVQo5jre7KhLLqTmAAz9ow=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:mime-version:content-type;
	b=iLhHcaorWDo+9m+Rg/6y3Kms5AvpRBZgvQLU/X/FXDdxZvpDdnfQB8j9DsBYY3lqhfbTYltwISt5NUpdWFK3irXDFgEXEHcQUjBDgEw3ayXcGI2FcVp4PwHDB/dar1/AyQYcQAAcBzveIj+3wEggkCKzX0PWNM8e1ZAHcoWfVJU=
Received: by 10.78.51.9 with SMTP id y9mr2514342huy.58.1209044090121;
	Thu, 24 Apr 2008 06:34:50 -0700 (PDT)
Received: by 10.78.118.8 with HTTP; Thu, 24 Apr 2008 06:34:50 -0700 (PDT)
Message-ID: <1a027d210804240634w4ee63ccdi251757231d3827bf@mail.gmail.com>
Date: Thu, 24 Apr 2008 19:04:50 +0530
From: "Anoop G" <anoopmadavoor@gmail.com>
To: pgsql-sql@postgresql.org
Subject: How I can check a substring is a valid number in postgresql ?
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_7980_29135660.1209044090089"
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: 200804/154
X-Sequence-Number: 30693

------=_Part_7980_29135660.1209044090089
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Dear All,

    How I can check a substring is a valid number in postgresql ?


example:

I have a query

select max(substring(code,2,length(code))::INT) from emp where
substring(code,0,2) = 'A';



code
========
A0001
A0002
ABC005
S0002

This query fails because of ABC002, how I can avoid this error , if there is
a any way to check substring(code,1,length(code))  is a number ,then i can
correct the query by put it in where condition.

I want to get the result as 2(ie want to avoid checking ABC005

is there is any built in function to check the substring is a number in
postgresql?

Iam using  postgres 8.1.8

 pls help me


thanks in advance:

Anoop

------=_Part_7980_29135660.1209044090089
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

<span class="HcCDpe">Dear All,<br>
<br>
&nbsp;&nbsp;&nbsp; How I can check a substring is a valid number in postgresql ?<br>
<br>
<br>
example:<br>
<br>
I have a query <br>
<br>
select max(substring(code,2,length(code))::INT) from emp where substring(code,0,2) = &#39;A&#39;; <br>
<br>
<br>
<br>
code <br>
========<br>
A0001<br>
A0002<br>
ABC005<br>
S0002<br>
<br>
This query fails because of </span><span class="HcCDpe">ABC002, how I can avoid this error , if there is a any way to check </span><span class="HcCDpe">substring(code,1,length(code))&nbsp; is a number ,then i can correct the query by put it in where condition.<br>

<br>
I want to get the result as 2(ie want to avoid checking ABC005<br>
<br>
is there is any built in function to check the substring is a number in postgresql?<br>
<br>
Iam using&nbsp; postgres 8.1.8 <br>
<br>
&nbsp;pls help me <br><br>
<br>
thanks in advance:<br>
<br>
Anoop</span>

------=_Part_7980_29135660.1209044090089--