Using replace in postgresql

Question

I have the following SQL query using replace

SELECT 
            replace(
            replace(
            replace(
            replace(
            replace(email_text , 'This email is to notify you that for one or more records the Clearance has been Requested. Please follow the link below to go directly to this record.', '') 
              , ':', '</th>:<th>')  
              , '<BR/></th>','<BR/>') 
              , 'http</th>:<th>', 'http:') 
              , '</ul>', '</th></ul>') 
            INTO  v_result 
                FROM   record.notification  
                WHERE record_notification_id = p_recordnotification_id;

in above code I have

replace(email_text , 'This email is to notify you that for one or more records the Clearance has been Requested. Please follow the link below to go directly to this record.', '')

Now instead I want to use like replace email_text with the sentence that starts with 'This email' and ends with 'this record.' instead of writing complete sentence . Please suggest.

Grah, why?! What are you actually trying to do here - get the plain text out of HTML? — Craig Ringer
– Craig Ringer, Commented Jul 9, 2014 at 7:15

Bohemian · Accepted Answer · 2014-07-09 08:34:04Z

2

Use regex:

regex_replace(email_text, 'This email.*this record[.]', '')

answered Jul 9, 2014 at 8:34

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using replace in postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related