Postrgres Regex to replace part of a String

Question

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")

@craig-ringer Do you know a better way? Please share with us. We are many programers searching it. dba.stackexchange.com/questions/54663/… stackoverflow.com/questions/18209625/… — Emilio Platzer
– Emilio Platzer, Commented Aug 13, 2015 at 14:57
@EmilioPlatzer If it is actually json, use PostgreSQL's json support to unpack the json and iterate over the key/value pairs. If you are on an older version, you can use pl/python or pl/perl and a json library. — Craig Ringer
– Craig Ringer, Commented Aug 13, 2015 at 23:34

Emilio Platzer · Accepted Answer · 2015-08-12 19:43:51Z

You can use:

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

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

Notes:

\" inside strings are Ok!
there is no way of detect internals ocurences
\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

desertnaut · Accepted Answer · 2024-12-29 23:40:34Z

0

If you're trying to remove special characters and numbers from a NAME column and want only:

Alphabets
Dots (.) (to retain names like Mr. Karan, Dr. Deepak)
Spaces (useful if you're concatenating first and last name columns, or for full names like Karan Ahuja, Deepak Patil),

you can use the following PostgreSQL query:

REGEXP_REPLACE("NAME", '[^a-zA-Z.\s]', '', 'g') AS "NAME"

Example:

Original Name         Processed Name
Mr. Karan123!         Mr. Karan
Dr.Deepak@! Patil     Dr. Deepak Patil
Karan**&%$ Ahuja123   Karan Ahuja

This query removes any unwanted characters while retaining the desired format.

edited Dec 29, 2024 at 23:40

desertnaut

60.8k32 gold badges155 silver badges183 bronze badges

answered Dec 19, 2024 at 5:48

Khushiiee

12 bronze badges

Collectives™ on Stack Overflow

Postrgres Regex to replace part of a String

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related