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:
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

Search Discussions

Discussion Posts

Previous

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 2 of 2 | next ›
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedMar 9, '09 at 2:48p
activeMar 12, '09 at 6:44p
posts2
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Justin Funk: 1 post Lubomir Petrov: 1 post

People

Translate

site design / logo © 2022 Grokbase