PostgreSQL - REGEXP_REPLACE Function

In PostgreSQL, the REGEXP_REPLACE function is used to replace substrings that match a POSIX regular expression pattern within a string. This function can be quite powerful when used for string manipulation tasks that require pattern matching.

Syntax:

REGEXP_REPLACE(source_string, pattern, replacement [, flags])

source_string: The original string where replacements will be made.
pattern: The POSIX regular expression pattern that you're looking for in the source string.
replacement: The string that will replace each substring that matches the pattern.
flags: Optional. It's a string consisting of one or more of the following characters that change the matching behavior:
- i: Makes the match case-insensitive.
- g: Replaces all matches in the string (global), not just the first one.
- m: Treats the source string as multiple lines. Causes ^ and $ to match the start/end of each line.
- n: Matches any newline character (\n, \r, etc.).
- p: Permits POSIX syntax (default).
- w: Ignores whitespace and allows comments within patterns.
- x: Extends syntax (similar to p but more liberal).

Examples:

Basic Usage:
Replacing the first sequence of digits with the word "NUMBER":
```
SELECT REGEXP_REPLACE('The price is 123 and the code is 456', '\d+', 'NUMBER');
```
Result: 'The price is NUMBER and the code is 456'
Using the Global Flag:
To replace all sequences of digits:
```
SELECT REGEXP_REPLACE('The price is 123 and the code is 456', '\d+', 'NUMBER', 'g');
```
Result: 'The price is NUMBER and the code is NUMBER'
Case-insensitive Replacement:
Replace all occurrences of the word "hello", regardless of case, with "hi":
```
SELECT REGEXP_REPLACE('Hello there! hello again!', 'hello', 'hi', 'gi');
```
Result: 'Hi there! hi again!'
Using Capturing Groups:
You can use parentheses in the pattern to create capturing groups and reference them in the replacement string using \1, \2, etc.:
```
SELECT REGEXP_REPLACE('John Smith and Jane Doe', '(\w+) (\w+)', '\2, \1', 'g');
```
Result: 'Smith, John and Doe, Jane'
Here, \1 refers to the first capturing group and \2 refers to the second.

Notes:

REGEXP_REPLACE operates from left to right. Without the 'g' flag, only the first match is replaced.
Using capturing groups can be extremely useful for rearranging matched content or including parts of the matched content in the replacement.

In conclusion, the REGEXP_REPLACE function in PostgreSQL offers a robust way to replace substrings based on pattern matching, providing both basic and advanced replacement capabilities.

How to use REGEXP_REPLACE function in PostgreSQL:
- Use REGEXP_REPLACE for pattern-based string replacement.
```
SELECT REGEXP_REPLACE('Hello, World!', 'Hello', 'Hi');
```
Replacing substrings with REGEXP_REPLACE in PostgreSQL:
- Replace specific substrings based on a regular expression.
```
SELECT REGEXP_REPLACE('123-456-7890', '\d', 'X', 'g');
```
Using capturing groups with REGEXP_REPLACE in PostgreSQL:
- Leverage capturing groups for targeted replacements.
```
SELECT REGEXP_REPLACE('John Doe (30)', '(\w+) (\w+) $(\d+)$', '\3 \1 \2');
```
Replacing multiple occurrences with REGEXP_REPLACE:
- Replace multiple occurrences of a pattern within a string.
```
SELECT REGEXP_REPLACE('apple,apple,orange', 'apple', 'banana', 'g');
```
Using REGEXP_REPLACE with case sensitivity in PostgreSQL:
- Control case sensitivity in pattern matching.
```
SELECT REGEXP_REPLACE('Hello, world!', 'hello', 'Hi', 'i');
```

SELECT STRING_TO_ARRAY(REGEXP_REPLACE('apple,apple,orange', 'apple', 'banana', 'g'), ',');

SELECT * FROM your_table WHERE REGEXP_REPLACE(column_name, 'pattern', 'replacement') = 'desired_value';