Thread: Is there any function to test for numeric ips?
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
> -----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.
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}$'
>
> 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}$'
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
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