SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to replace single whitespace with underscore in a certain part of a longer text

CarstenDDApr 17 2022

Hello,
I'm using Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production.
I try to replace single whitespace in a certain part of a longer text. I have

SELECT REGEXP_REPLACE('       {
           "Quantity": 5,
           "ObjectTypeId": 1,
           "Name": "IT Equipment (Hardware)",
           "Manufacturer": "Other",
           "NetPricePerObject": 623.7000000000,
           "SerialNumber": null,
           "Details": "HP ProDesk 400 G7 MT i5-10500 (DE)"
       },'
,'("Details": ")([[:space:]])(")','\1_\3')
FROM DUAL

As result I expect:
      {
           "Quantity": 5,
           "ObjectTypeId": 1,
           "Name": "IT Equipment (Hardware)",
           "Manufacturer": "Other",
           "NetPricePerObject": 623.7000000000,
           "SerialNumber": null,
           "Details": "HP_ProDesk_400_G7_MT_i5-10500_(DE)"
       },
I have tried really a lot of search strings but I don't get it to work.
With Oracle12 I didn't find a command for manipulation a JSON, so I tried to use regular expression.
All help is appreciated.
Regards Carsten

This post has been answered by Frank Kulash on Apr 17 2022

Jump to Answer

Added on Apr 17 2022

6 comments

2,625 views

3 mentions