Insert a string at a specific position in PostgreSQL

Question

I have the following query

SELECT email_text INTO  v_result 
    FROM  test.record_notification 
    WHERE record_notification_id = p_recordnotification_id;

which returns the below result

[br/] [br/] [br/]Record Id : Record Name : Title Id : Title Name[br/][ul]http://localhost:8080/myproject/ShowRecords.action?recordsVO.recordId=3324&recordId=3324&status =Record Requested >3324 : DEV: test contract : 2448730 : Titanic[/ul]

However I want to add "[th]" "[/th]" in the result above so that the final string becomes

[br/] [br/] [br/][th]Record Id : Record Name : Title Id : Title Name[br/][/th][th]http://localhost:8080/myproject/ShowRecords.action?recordsVO.recordId=3324&recordId=3324&status =Record Requested >3324 : DEV: test contract : 2448730 : Titanic[/th]

please note that there can be multiple records like this .

Please suggest a way in PostgreSQL

Just code · Accepted Answer · 2014-06-25 12:52:15Z

1

Replace your query by

SELECT '[br/] [br/] [br/]<th>' || replace(replace(email_text,'[br/]',''),'ul','th') INTO  v_result 
    FROM  test.record_notification 
    WHERE record_notification_id = p_recordnotification_id;

I am assuming that you have a problem of one record only thanks

[Edit]

If you are not worring about your br then just do

 SELECT '<th>' || replace(replace(email_text,'[br/]',''),'ul','th') INTO  v_result 
        FROM  test.record_notification 
        WHERE record_notification_id = p_recordnotification_id;

It will work in a 99% case

edited Jun 25, 2014 at 12:52

answered Jun 25, 2014 at 12:30

Just code

13.8k10 gold badges54 silver badges94 bronze badges

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

3 Comments

Just code Over a year ago

@ronan well,its for the multiple ones you need to make one function or something you wrong for the one only so i give you an answer. it should be customize by your requirement

ronan Over a year ago

@just_code could you be show me how it can be done for multiple records ?

Just code Over a year ago

@ronan not sure as of now.Because you don't have fix input from user already i was afraid about multiple ones. you should analyze your inputs from user and then add replace functions or alternate ones for this.this could be your answer

Collectives™ on Stack Overflow

Insert a string at a specific position in PostgreSQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related