CODEWITHSUNDEEP
regexpostgresql
Ayush
Ayush

Reputation: 161

Need to form pattern for regexp_replace

I have input string something like :

1.2.3.4_abc_4.2.1.44_1.3.4.23
100.11.11.22_xyz-abd_10.2.1.2_12.2.3.4
100.11.11.22_xyz_123_10.2.1.2_1.2.3.4

I have to replace the first string formed between two ipaddress which are separated by _, however in some string the _ is part of the replacement string (xyz_123)

I have to find the abc, xyz-abd and xyz_123 from the above string, so that I can replace with another column in that table.

Upvotes: 1

Views: 122

Answers (3)

Ayush
Ayush

Reputation: 161

In order to map match first two "" , as @stema and @Tim Pietzcker mentioned the regex works. Then in order to append "" to the column , which is what I was struggling with, can be done with || operator as eg below

update table1 set column1=regexp_replace(column1,'.*?(?=\d+.)','' || column2 || '_')

Then for using the another table for update query , the below eg can be helpfull

update table1 as t set column1=regexp_replace(column1,'.*?(?=\d+.)','' || column2 || '_') from table2 as t2 where t.id=t2.id [other criteria]

Upvotes: 0

stema
stema

Reputation: 93036

Probably this is enough:

_[^.]+_

and replace with

_Replacement_

See it here on Regexr.

[^.]+ uses a negated character class to match a sequence of at least one (the + quantifier) non "." characters.

I am also matching a leading and a trailing "_", so you have to put it in again in the replacement string.

If PostgreSQL supports lookbehind and lookahead assertions, then it is possible to avoid the "_" in the replacement string:

(?<=_)[^.]+(?=_)

See it on Regexr

Upvotes: 2

Tim Pietzcker
Tim Pietzcker

Reputation: 336468

_.*?_(?=\d+\.)

matches _abc_, _xyz-abd_ and _xyz_123_ in your examples. Is this working for you?

DECLARE
    result VARCHAR2(255);
BEGIN
    result := REGEXP_REPLACE(subject, $$_.*?_(?=\d+\.)$$, $$_foo_$$);
END;

Upvotes: 2

Related Questions