postgresql

Postrgres Regex to replace part of a String


Is there a regex that could be used in the update statement that would replace a part of a String?

I have a row that has a consistent string across all the rows, so I'm looking for a regex pattern that would look for a specific pattern and replace that? For example, say I have a column row that has the following value:

{
  "test": "Some Value"
  ....
}

What I'm essentially looking for is to match anything with "test": and replace that with whatever I give (for example., "test": "Some Other Value")


Solution

  • You can use:

    regexp_replace(text1, '("test":\s*")(([^"]|\\")*)(")', '\1' || 'other value' || '\4' , 'g')
    

    But, warning! The regexp replaces inner ocurences of "test": value.

    Notes:

    1. \" inside strings are Ok!
    2. there is no way of detect internals ocurences
    3. \1 & \4 are captures of the regexp, in this case you could put directly '"test": ' instead of '\1' and '"' instead of '\4'. But \n is more general

    A complete example:

    drop table if exists example;
    
    create table example(json1 json, text1 text);
    
    insert into example (json1, text1) values (
      '{"in":{"test": "inner"}, "test": "outer", "other": "some"}',
      '{"in":{"test": "inner"}, "test": "outer", "other": "some"}'
    );
    
    insert into example (json1, text1) values (
      '{"in":{"test": "inner \"no\""}, "test": "outer \"yes\"", "other": "some"}',
      '{"in":{"test": "inner \"no\""}, "test": "outer \"yes\"", "other": "some"}'
    );
    
    select * from example;
    
    select regexp_replace(text1, '("test":\s*")(([^"]|\\")*)(")', '\1' || 'other value' || '\4' , 'g')
      from example;
    

    If you use postgres 9.4 you can use json or jsonb type for a column