PostgreSQL fetch a part of string

Question

I am storing a log like this:

10.03.2015 11:45:29-JAQ -- Updated Following Information
Mobile Updated From 0 to 0502770020</br>
Company Industry Id Updated From 747 to 753</br>
Name Updated from futurehomes to futurehomesdubai </br>
Company Head Office Updated From 0 to 1</br>
Company Telephone Updated From 8008436699 to
Company Comments Updated From Offices and Hotel (Under Construction) to  Offices and Hotel (Under Construction) info@futurehomesdubai.com futurehomesdubai.com</br>
Company Toll Free No Updated From to 800-8436699</br>
Company(139618) Industry Sub Section Updated </br>

I am storing this in a text field. From this I need to display only Name Updated from futurehomes to futurehomesdubai section. the futurehomes to futurehomesdubai will change according to company names. How can I do this?

My expected output is Name Updated from futurehomes to futurehomesdubai — Pradeep.T.P, Oct 13 '15 at 05:01
yes. "futurehomes to futurehomesdubai" part will change every record. — Pradeep.T.P, Oct 13 '15 at 05:09
in single row. we need to fetch it by the pattern. like starting from "Name Update" to End "". I don't know to write this pattern.... — Pradeep.T.P, Oct 13 '15 at 05:15

Vivek S. · Accepted Answer · 2015-10-13T05:34:13.983

with the following as example:

create table logs(tlog text);

insert into logs values ('Mobile Updated From 0 to 0502770020</br>Company Industry Id Updated From 747 to 753</br>Name Updated from futurehomes to futurehomesdubai </br>Company Head Office Updated From 0 to 1</br>Company Telephone Updated From 8008436699 to
Company Comments Updated From Offices and Hotel (Under Construction) to  Offices and Hotel (Under Construction)info@futurehomesdubai.com futurehomesdubai.com</br>Company Toll Free No Updated From to 800-8436699</br>Company(139618) Industry Sub Section Updated </br>');

and the select should be

select * 
from (
     select unnest(regexp_split_to_array(tlog,'</br>')) col 
     from logs
     )t
where col ilike 'Name Updated%'

Result:

col                                                
-------------------------------------------------- 
Name Updated from futurehomes to futurehomesdubai

PostgreSQL fetch a part of string

1 Answers1