pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedIs there any function to test for numeric ips?
6+ messages / 4 participants
[nested] [flat]
* Is there any function to test for numeric ips?
@ 2009-12-11 16:05 Oliveiros C, <oliveiros.cristina@marktest.pt>
0 siblings, 3 replies; 6+ messages in thread
From: Oliveiros C, @ 2009-12-11 16:05 UTC (permalink / raw)
To: pgsql-sql
Dear All,
I have a table with host names and some happen to be numeric IPs.
I would like to be able to filter out the later.
Is there any function pre-defined in the system that can test a particular text type value to see if it is a numeric ip?
Something that returns true if applied to '192.168.1.1' but false if applied to 'videos.sapo.pt' ?
I considered NOT LIKE '%.%.%.%' but I'm affraid it will filter out host names like 'www.google.com.br'
I've realized that, for ex, inet 'x.x.x.x' will fail if the input is not a numeric IP,
is there any simple and direct way to somewhat trap that error and convert it to a false value that can be used
in a WHERE clause?
This is not to be included in a program, I'm just interested in a query that I can do directly to the back end on a client program like pgAdmin.
I know very little ( nothing, actually...) about function creation in pgplsql, so, before investing in learning, I would like to know if there is any built in function that does the trick, so I can skip the (potential) learning curve.
So far, I've googled up and down but found none
Thanx in advance for your kind and valuable help
Best,
Oliveiros
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Is there any function to test for numeric ips?
@ 2009-12-11 16:20 Fernando Hevia <fhevia@ip-tel.com.ar>
parent: Oliveiros C, <oliveiros.cristina@marktest.pt>
2 siblings, 1 reply; 6+ messages in thread
From: Fernando Hevia @ 2009-12-11 16:20 UTC (permalink / raw)
To: 'Oliveiros C,' <oliveiros.cristina@marktest.pt>; pgsql-sql
> -----Mensaje original-----
> De: de Oliveiros C,
>
> Dear All,
>
> I have a table with host names and some happen to be numeric IPs.
>
> I would like to be able to filter out the later.
>
> Is there any function pre-defined in the system that can test
> a particular text type value to see if it is a numeric ip?
>
> Something that returns true if applied to '192.168.1.1' but
> false if applied to 'videos.sapo.pt' ?
>
> I considered NOT LIKE '%.%.%.%' but I'm affraid it will
> filter out host names like 'www.google.com.br'
>
> I've realized that, for ex, inet 'x.x.x.x' will fail if the
> input is not a numeric IP, is there any simple and direct way
> to somewhat trap that error and convert it to a false value
> that can be used in a WHERE clause?
>
You could filter IP out with a regular expression:
select hostname as hosts_not_ip
from table
where hostname !~ '^[0-9]\.[0-9]\.[0-9]\.[0-9]$'
Cheers,
Fernando.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Is there any function to test for numeric ips?
@ 2009-12-11 16:23 Harald Fuchs <hari.fuchs@gmail.com>
parent: Oliveiros C, <oliveiros.cristina@marktest.pt>
2 siblings, 0 replies; 6+ messages in thread
From: Harald Fuchs @ 2009-12-11 16:23 UTC (permalink / raw)
To: pgsql-sql
In article <E94D918326D044C69895D829038E31B2@marktestcr.marktest.pt>,
"Oliveiros C," <oliveiros.cristina@marktest.pt> writes:
> Dear All,
> I have a table with host names and some happen to be numeric IPs.
> I would like to be able to filter out the later.
> Is there any function pre-defined in the system that can test a particular text
> type value to see if it is a numeric ip?
> Something that returns true if applied to '192.168.1.1' but false if applied to
> 'videos.sapo.pt' ?
> I considered NOT LIKE '%.%.%.%' but I'm affraid it will filter out host names
> like 'www.google.com.br'
> I've realized that, for ex, inet 'x.x.x.x' will fail if the input is not a
> numeric IP,
> is there any simple and direct way to somewhat trap that error and convert it
> to a false value that can be used
> in a WHERE clause?
You could use a regular expression match:
SELECT host
FROM t1
WHERE host ~ '^\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}$'
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Is there any function to test for numeric ips?
@ 2009-12-11 16:26 Fernando Hevia <fhevia@ip-tel.com.ar>
parent: Fernando Hevia <fhevia@ip-tel.com.ar>
0 siblings, 0 replies; 6+ messages in thread
From: Fernando Hevia @ 2009-12-11 16:26 UTC (permalink / raw)
To: 'Fernando Hevia' <fhevia@ip-tel.com.ar>; 'Oliveiros C,' <oliveiros.cristina@marktest.pt>; pgsql-sql
>
> You could filter IP out with a regular expression:
>
> select hostname as hosts_not_ip
> from table
> where hostname !~ '^[0-9]\.[0-9]\.[0-9]\.[0-9]$'
>
Oops, i missed something. Its:
select hostname as hosts_not_ip
from table
where hostname !~ '^[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}$'
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Is there any function to test for numeric ips?
@ 2009-12-11 16:28 Alvaro Herrera <alvherre@commandprompt.com>
parent: Oliveiros C, <oliveiros.cristina@marktest.pt>
2 siblings, 1 reply; 6+ messages in thread
From: Alvaro Herrera @ 2009-12-11 16:28 UTC (permalink / raw)
To: Oliveiros C, <oliveiros.cristina@marktest.pt>; +Cc: pgsql-sql
Oliveiros C, wrote:
> I've realized that, for ex, inet 'x.x.x.x' will fail if the input is not a numeric IP,
> is there any simple and direct way to somewhat trap that error and convert it to a false value that can be used
> in a WHERE clause?
Yes, you can create a plpgsql function with an exception block; return
false inside the exception, true otherwise. It looks something like
begin
perform $1::inet;
return true;
exception
when invalid_something then
return false
end;
I don't remember the exact syntax and the exception name but that should
get you started.
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Is there any function to test for numeric ips?
@ 2009-12-11 17:40 Oliveiros C, <oliveiros.cristina@marktest.pt>
parent: Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 0 replies; 6+ messages in thread
From: Oliveiros C, @ 2009-12-11 17:40 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; hari.fuchs@gmail.com; +Cc: pgsql-sql; fhevia@ip-tel.com.ar
Howdy, Alvaro, Fernando and Harald.
Thanks a lot for your prompt reply.
As I'm in a big hurry with this I used the regular expressions thing, it
worked fine.
But your solution looks good as well, Alvaro, the problem is my poor
knowledge on pgplsql
Definitely, I have to study in more depth subjects like pgplsql not to
mention regular expressions
Thanx a lot for your fast help,
Best,
Oliveiros
----- Original Message -----
From: "Alvaro Herrera" <alvherre@commandprompt.com>
To: "Oliveiros C," <oliveiros.cristina@marktest.pt>
Cc: <pgsql-sql@postgresql.org>
Sent: Friday, December 11, 2009 4:28 PM
Subject: Re: [SQL] Is there any function to test for numeric ips?
> Oliveiros C, wrote:
>
>> I've realized that, for ex, inet 'x.x.x.x' will fail if the input is not
>> a numeric IP,
>> is there any simple and direct way to somewhat trap that error and
>> convert it to a false value that can be used
>> in a WHERE clause?
>
> Yes, you can create a plpgsql function with an exception block; return
> false inside the exception, true otherwise. It looks something like
>
> begin
> perform $1::inet;
> return true;
> exception
> when invalid_something then
> return false
> end;
>
> I don't remember the exact syntax and the exception name but that should
> get you started.
>
> --
> Alvaro Herrera
> http://www.CommandPrompt.com/
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2009-12-11 17:40 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-12-11 16:05 Is there any function to test for numeric ips? Oliveiros C, <oliveiros.cristina@marktest.pt>
2009-12-11 16:20 ` Fernando Hevia <fhevia@ip-tel.com.ar>
2009-12-11 16:26 ` Fernando Hevia <fhevia@ip-tel.com.ar>
2009-12-11 16:23 ` Harald Fuchs <hari.fuchs@gmail.com>
2009-12-11 16:28 ` Alvaro Herrera <alvherre@commandprompt.com>
2009-12-11 17:40 ` Oliveiros C, <oliveiros.cristina@marktest.pt>
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