1. Replace the target
1). contact field type varchar.
2). Remove two consecutive digits from the field, and the number after each equal sign is different, effective_caller_id_name=051066824513,effective_caller_id_number=051066824513.
2. Querying the contents of the original field
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 substitution
Select Regexp_replace (Contact, ' (effective_caller_id_name=\d+,) +? ', ', ' 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 the Replace operation
Update pbx_agents Set contact = regexp_replace (Contact, ' (effective_caller_id_name=\d+,) +? ', ', ' 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 "
PostgreSQL varchar field regexp_replace regular substitution