Re: Get IP addresses from tsvectors - Mailing list pgsql-general

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
>