EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL REGEXP_REPLACE
 

PostgreSQL REGEXP_REPLACE

Updated May 26, 2023

PostgreSQL REGEXP_REPLACE

 

 

Introduction to PostgreSQL REGEXP_REPLACE

The regular expression is a sequence of characters, the short name for the list of strings. If any string matches with any of the strings, which is part of a list of the strings 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 uses a POSIX regular expression pattern.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax:

Consider the following syntax.

REGEXP_REPLACE(input_string, regex_pattern, replace_string,[, flags])

Explanation:

  1. input_string: This defines the input string in which replacement should be taken place for a specified pattern, a POSIX regular expression.
  2. regex_pattern: This defines the POSIX regular expression to match the string.
  3. flags: This flag is used to control the behavior of the REGEXP_REPLACE() function, This can have the value of one or more characters.
  4. 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 of implementing 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’

Suppose we want to re-arrange the last name and the first name for 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.

PostgreSQL REGEXP_REPLACE 1

Example #2

Example, to remove the string, consider we have a string in the 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.

PostgreSQL REGEXP_REPLACE 2

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.

PostgreSQL REGEXP_REPLACE 3

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 just the 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.

Output 4

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 above INSERT statement’s result using the following SQL statement and snapshot.

select * from student;

Output 5

Consider the following SQL statement where we are checking whether the stud_lname is having ‘Jo’ substring, and if it exists, then we replace 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.

Output 6

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 replace 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.

Output 7

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 with the new string.

3. We can use The PostgreSQL REGEXP_REPLACE() function, the substring in variable length or dynamic strings.

Conclusion

From the above article, we hope you understand 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

We hope that this EDUCBA information on “PostgreSQL REGEXP_REPLACE” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. SQL Administration
  2. PostgreSQL List Users
  3. MySQL Constraints
  4. MySQL Self Join

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW