Get IP addresses from tsvectors
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@iastate.edu':4
into
IP_ADDRESSES
-------------------------
65.110.236.113
10.10.94.126
Thanks for the help...
Justin Funk
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@iastate.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:
Show quoted text
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@iastate.edu':4into
IP_ADDRESSES
-------------------------
65.110.236.113
10.10.94.126Thanks for the help...
Justin Funk