Find LETTERS-NUMBER pairs in postgres using regex

user_2894403 0
Viewed 78

I need to replace TEXT1-NUMBER with TEXT2-NUMBER. Example "These are TEXT1-123 and TEXT1-456 examples" should be replaced with "These are TEXT2-123 and TEXT2-456 examples".

I can replace most of the cases using

Regexp_Replace(column_name, '(\mTEXT1)(-[0-9]+\M)', 'TEXT2\2', 'g')

But it also replaces some cases that I want to exclude, such as

  • TEXT1-NUMBER-NUMBER
  • TEXT3-NUMBER-TEXT1-NUMBER

How can I make it to match only exact pairs of TEXT-NUMBER?

Thanks.

regexp-replace postgresql regex
1 Answers

You can use

SELECT REGEXP_REPLACE(column_name,
                      '(\s|^)TEXT1(-[0-9]+)(?!\S)',
                      '\1TEXT2\2', 'g') AS Result;

See the regex demo.

Beginning with PostgreSQL 10, lookbehinds are supported, and you can also use REGEXP_REPLACE(column_name, '(?<!\S)TEXT1(-[0-9]+)(?!\S)', 'TEXT2\1', 'g') then.

Regex details:

  • (\s|^) - Group 1 (\1 refers to this value): a whitespace or start of string
  • TEXT1 - a static string -(-[0-9]+) - Group 2 (\2 refers to this value): - and one or more digits
  • (?!\S) - a negative lookahead that fails the match if there is no non-whitespace char immediately to the right of the current location.
user_38329700
Related