Postgres regexp_replace return on pattern match failure

Question

I'm attempting to write a sql script in postgres. I have two columns: Column A and Column B. Both columns are strings. Column B represents a prefix which was appended to Column A (if B wasn't null). Example: If Column B had "PA" and the value to be added to Column A was "102", then Column A will now contain "PA102." I want to update a new Column C with the value of Column A without the prefix.

I was attempting to use the following:

regexp_replace(COLUMN_A, COLUMN_B, '')

The documentation for regexp_replace (below) says:

"If the pattern does not match, the function returns no rows."

I need for the method to return the whole string from Column A if Column B doesn't match. Is there some other method (or maybe a flag for regexp_replace) which would accomplish this?

Thanks in advance.

regexp_replace docs:

http://www.postgresql.org/docs/9.1/static/functions-matching.html#FUNCTIONS-POSIX-REGEXP

John Powell · Accepted Answer · 2014-08-12 17:17:17Z

1

update  mytable set column_c = (select regexp_replace(column_a, column_b, '')  from mytable);

works for me, with the key being that you do the regexp_replace as a sub query, and use this for the update.

answered Aug 12, 2014 at 17:17

John Powell

12.6k6 gold badges64 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Emery Lapinski · Accepted Answer · 2015-04-09 15:02:52Z

0

Something like this might work. It will return null on no match, then you could wrap it in a coalesce() to fill it in with the original column.

select (select regexp_replace(COLUMN_A, COLUMN_B, ''))

From: http://www.postgresql.org/message-id/[email protected]

answered Apr 9, 2015 at 15:02

Emery Lapinski

1,6621 gold badge19 silver badges28 bronze badges

Collectives™ on Stack Overflow

Postgres regexp_replace return on pattern match failure

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related