pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

How I can check a substring is a valid number in postgresql ?
3+ messages / 3 participants
[nested] [flat]

* How I can check a substring is a valid number in postgresql ?
@ 2008-04-24 13:34  Anoop G <anoopmadavoor@gmail.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Anoop G @ 2008-04-24 13:34 UTC (permalink / raw)
  To: pgsql-sql

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

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: How I can check a substring is a valid number in postgresql ?
@ 2008-04-24 13:53  Volkan YAZICI <yazicivo@ttmail.com>
  parent: Anoop G <anoopmadavoor@gmail.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Volkan YAZICI @ 2008-04-24 13:53 UTC (permalink / raw)
  To: Anoop G <anoopmadavoor@gmail.com>; +Cc: pgsql-sql

On Thu, 24 Apr 2008, "Anoop G" <anoopmadavoor@gmail.com> writes:
> How I can check a substring is a valid number in postgresql ?

SELECT col ~ '[0-9]+';


Regards.



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: How I can check a substring is a valid number in postgresql ?
@ 2008-04-24 15:43  TJ O'Donnell <tjo@acm.org>
  0 siblings, 0 replies; 3+ messages in thread

From: TJ O'Donnell @ 2008-04-24 15:43 UTC (permalink / raw)
  To: pgsql-sql

This regular expression works for numeric/float values as
well as integers.  It allows for exponents.

tvalue ~ E'^[+-]?[0-9]+(\\\\.[0-9]*)?([Ee][+-]?[0-9]+)?\$'

TJ O'Donnell
http://www.gnova.com/



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2008-04-24 15:43 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-04-24 13:34 How I can check a substring is a valid number in postgresql ? Anoop G <anoopmadavoor@gmail.com>
2008-04-24 13:53 ` Volkan YAZICI <yazicivo@ttmail.com>
2008-04-24 15:43 Re: How I can check a substring is a valid number in postgresql ? TJ O'Donnell <tjo@acm.org>

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