Re: Get IP addresses from tsvectors

Hi,

Maybe you can use something like the following:

test=# select * from t1;
t
-----------------------------------------------------------------------
'leas':4 'return':2
'leas':2 'found':1 'address':5 'hardwar':4 '65.110.236.113':6
'00':3 '17':4 '1d':7 '27':6 '4c':8 'f2':5 'via':9 'free':14 'leas':15
'martin':12 'network':11 'dhcpdiscov':1 '10.10.94.126':10
'leas':2 'found':1 'address':5 'request':4 '65.110.236.113':6
'ip':4 'leas':2,5 'ident':7 'hardwar':1
'leas':2 'choos':1 'address':5 'request':4
'leas':2 'return':1 '65.110.236.113':3
'00':3 '17':4 '1d':7 '27':6 '4c':8 'f2':5 'via':9 'dhcpdiscov':1
'10.10.94.126':10
'00':5 '17':6 '1d':9 '27':8 '4c':10 'f2':7 'via':11 'dhcpoffer':1
'10.10.94.126':12 '65.110.236.113':3
'451':6 'tri':9 '4.7.1':7 'later':11 'pleas':8 'milter':2 'reject':5
'tempfail':12 'n29c3q08020087':1 'kgander(at)iastate(dot)edu':4
(14 rows)

test=#
test=#
test=# select
test-# distinct ip_address
test-# from
test-# (select substring(t from E'\\d+\\.\\d+\\.\\d+\\.\\d+') as
ip_address from t1) as t
test-# where ip_address is not null;
ip_address
----------------
10.10.94.126
65.110.236.113
(2 rows)

test=#
test=#

Of course you should make the regular expression stricter, but this is
the idea.

Hope that helps.

Regards,
Lubomir Petrov

Justin Funk wrote:
> Greetings,
>
> I have a table with a column with type tsvector. It contains the
> result of to_tsvector() of varchar field in the table. What I'd like
> to do is be able to search through the table and find all of the
> distinct IP addresses. Any idea how to turn:
>
> SELECT message_index_col FROM systemevents LIMIT 10;
> message_index_col
> ---------------------------------------------------------------------------------------------------------------------------------
> 'leas':4 'return':2
> 'leas':2 'found':1 'address':5 'hardwar':4 '65.110.236.113':6
> '00':3 '17':4 '1d':7 '27':6 '4c':8 'f2':5 'via':9 'free':14 'leas':15
> 'martin':12 'network':11 'dhcpdiscov':1 '10.10.94.126':10
> 'leas':2 'found':1 'address':5 'request':4 '65.110.236.113':6
> 'ip':4 'leas':2,5 'ident':7 'hardwar':1
> 'leas':2 'choos':1 'address':5 'request':4
> 'leas':2 'return':1 '65.110.236.113':3
> '00':3 '17':4 '1d':7 '27':6 '4c':8 'f2':5 'via':9 'dhcpdiscov':1
> '10.10.94.126':10
> '00':5 '17':6 '1d':9 '27':8 '4c':10 'f2':7 'via':11 'dhcpoffer':1
> '10.10.94.126':12 '65.110.236.113':3
> '451':6 'tri':9 '4.7.1':7 'later':11 'pleas':8 'milter':2 'reject':5
> 'tempfail':12 'n29c3q08020087':1 'kgander(at)iastate(dot)edu':4
>
> into
>
> IP_ADDRESSES
> -------------------------
> 65.110.236.113
> 10.10.94.126
>
> Thanks for the help...
>
> Justin Funk
>