Postgresql varchar field regexp_replace regular replacement

1. Replace the target

1) .contact field type varchar.

2)。 Remove two consecutive fields, each with a different number after the equal sign, effective_caller_id_name=051066824513,effective_caller_id_number=051066824513.

two。 Query the original field content

Select contact

From pbx_agents

Where contact ~ 'effective_caller_id_name=' limit 2

"{sip_append_audio_sdp=a=fmtp:18 annexb=no,call_timeout=60,effective_caller_id_name=051066824513,effective_caller_id_number=051066824513,cti_account=9,cti_agent_id=1102441276,cti_account_sid=5be394c3f8754bd89b9618937c687068,absolute_codec_string=\ 'OPUS,G729 (...)"

"{sip_append_audio_sdp=a=fmtp:18 annexb=no,call_timeout=60,effective_caller_id_name=051066824824,effective_caller_id_number=051066824824,cti_account=9,cti_agent_id=1103211003,cti_account_sid=4f3fae0e71b74bdaa3824e6ec7771815,absolute_codec_string=\ 'OPUS,G729 (...)"

3. Query statement validation replacement

Select regexp_replace (contact,'(effective_caller_id_name=\ dbath,) +?',','g')

From pbx_agents

Where contact ~ 'effective_caller_id_name='

"{sip_append_audio_sdp=a=fmtp:18 annexb=no,call_timeout=60,effective_caller_id_number=051066824513,cti_account=9,cti_agent_id=1102441276,cti_account_sid=5be394c3f8754bd89b9618937c687068,absolute_codec_string=\ 'OPUS,G729\'} sofia/gateway/agentProxy/1102441276"

"{sip_append_audio_sdp=a=fmtp:18 annexb=no,call_timeout=60,effective_caller_id_number=051066824824,cti_account=9,cti_agent_id=1103211003,cti_account_sid=4f3fae0e71b74bdaa3824e6ec7771815,absolute_codec_string=\ 'OPUS,G729\'} sofia/gateway/agentProxy/1103211003"

4. Perform a replace operation

Update pbx_agents set contact = regexp_replace (contact,'(effective_caller_id_name=\ dbath,) +?,','g')

Where contact ~ 'effective_caller_id_name='

Query returned successfully: 3929 rows affected, 505 msec execution time.

5. Query validation replacement

Select contact

From pbx_agents

"{sip_append_audio_sdp=a=fmtp:18 annexb=no,call_timeout=60,cti_account=9,cti_agent_id=1100891004,cti_account_sid=23869df09f2f47f0ae80a7cbc45e5185,absolute_codec_string=\ 'OPUS,G729\'} sofia/gateway/agentProxy/1100891004"

"{sip_append_audio_sdp=a=fmtp:18 annexb=no,call_timeout=60,cti_account=9,cti_agent_id=1102081069,cti_account_sid=9f2c1574fcb5497994cb9b892aee0d1c,absolute_codec_string=\ 'OPUS,G729\'} sofia/gateway/agentProxy/1102081069"