Introduction to PostgreSQL REGEXP_REPLACE
The regular expression is a sequence of characters which is the short name for the list of strings. If any string is matching with any of the string which is the part of a list of the strings which are defined by the regular expression. PostgreSQL supports the regular expression and the function provided by PostgreSQL is used to replace substrings with a new substring that matches a POSIX regular expression. The PostgreSQL REGEXP_REPLACE() function is using a POSIX regular expression pattern.
Syntax:
Consider the following syntax.
REGEXP_REPLACE(input_string, regex_pattern, replace_string,[, flags])
Explanation:
- input_string: This defines the input string which replacement should be taken place for a specified pattern which is a POSIX regular expression.
- regex_pattern: This defines the POSIX regular expression to match the string.
- flags: This flag is used to control the behavior of the REGEXP_REPLACE() function, This can have the value of one or more characters.
- Replace_string: This string defines a string that replaces the substring which matches the POSIX regular expression pattern.
Examples of PostgreSQL REGEXP_REPLACE
Here are the following examples to implement the PostgreSQL REGEXP_REPLACE function
Example #1 – Example to arrange the name
Consider the following name format like the first name and then last name:
‘Jacob David’
Now suppose we want to re-arrange the last name and the first name for some of the purposes like the last name and then the first name. So, we can use the PostgreSQL REGEXP_REPLACE() function to do this as follows:
SELECT REGEXP_REPLACE('Jacob David',
'(.*) (.*)',
'\2, \1');
Illustrate the result of the above statement by using the following snapshot.
Example #2
Example to remove the string, consider we have a string in following as follows:
“xyz54321ABC”
Now, we will remove all alphabets characters from the above string by using the following statement:
SELECT REGEXP_REPLACE('xyz54321ABC',
'[[:alpha:]]',
'',
'g');
Illustrate the result of the above statement by using the following snapshot.
Now, we will remove all digits from the above string by using the following statement:
SELECT REGEXP_REPLACE('xyz54321ABC',
'[[:digit:]]',
'',
'g');
Illustrate the result of the above statement by using the following snapshot.
In the above examples, we have used the following regular expressions.
'[[:alpha:]]'
and
'[[:digit:]]'
Also, we have used the replacement string as ‘’ and the flag ‘g’ we have used to instruct the PostgreSQL REGEXP_REPLACE function to replace all of the occurrences of the matched string and not the just first occurrence.
Example #3
Remove multiple occurrences of the spaces. Consider the following example which removes more than one space that occurred in a string. Consider the following statement to do the same.
SELECT REGEXP_REPLACE('PostgreSQL is awesome database',
'( ){2,}',
' ',
'g');
Illustrate the result of the above statement by using the following snapshot.
Example #4
We will create a table named ‘student’ by using the CREATE TABLE statement as follows:
create table student
(
stud_id serial PRIMARY KEY,
stud_fname VARCHAR(80) NOT NULL,
stud_lname VARCHAR(80) NOT NULL
);
Now, we will insert some data into the student table by using the INSERT INTO statement as follows
INSERT INTO student(stud_fname,stud_lname)
VALUES
('Smith','Johnson'),
('Williams','Jones'),
('Brown','Davis');
Illustrate the result of the above INSERT statement by using the following SQL statement and snapshot.
select * from student;
Consider the following SQL statement where we are checking whether the stud_lname is having ‘Jo’ substring and if it exists then we are replacing it with ‘K’
SELECT REGEXP_REPLACE(stud_lname , 'Jo', 'K') AS "New Name"
FROM student;
Illustrate the result of the above SQL statement by using the following snapshot.
Also, consider the other example,
Consider the following SQL statement where we are checking whether the stud_lname is having ‘s’ substring and if it exists then we are replacing it with ‘K’
SELECT REGEXP_REPLACE(stud_lname , 's', 'K') AS "New Name"
FROM student;
Illustrate the result of the above SQL statement by using the following snapshot.
Advantages of using PostgreSQL REGEXP_REPLACE () function
1. The PostgreSQL REGEXP_REPLACE () function supports various flags,
Consider examples like:
- flag ‘i’ : match case-insensitively
- flag ‘g’: search globally for each occurrence.
2. The PostgreSQL REGEXP_REPLACE() replaces all occurrences of the substring by the new string.
3. We can use The PostgreSQL REGEXP_REPLACE() function the substring in variable length or dynamic strings.
Conclusion
We hope from the above article you have understood how to use the PostgreSQL REGEXP_REPLACE() function and how the PostgreSQL REGEXP_REPLACE() function works. Also, we have added several examples of the PostgreSQL REGEXP_REPLACE() function to understand it in detail.
Recommended articles
This is a guide to PostgreSQL REGEXP_REPLACE. Here we discuss the examples to implement the PostgreSQL REGEXP_REPLACE function along with the Advantages. You may also look at the following articles to learn more –
