CODEWITHSUNDEEP
postgresql
joesan
joesan

Reputation: 15435

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

Upvotes: 1

Views: 886

Answers (2)

Khushiiee
Khushiiee

Reputation: 1

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.

Upvotes: 0

Emilio Platzer
Emilio Platzer

Reputation: 2479

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

Upvotes: 1

Related Questions