Users can verify that the substring “ joe” has been replaced with the “ peter” in the existing selected column. In this example, we replaced a string “joe” with the “peter” in the “email” column: SELECT REGEXP_REPLACE(email, 'joe', 'peter') AS "Final" FROM candidates Let’s utilize the REGEXP_REPLACE() function to alter the “email” column of the “candidates” table. The output shows that the “candidates” table has one record in it. To fetch the table’s data, we can execute the SELECT query as follows: SELECT * from candidates For better understanding, a table is carried out having some information in it. In PostgreSQL, the REGEXP_REPLACE() function can be used to replace the existing string with the new one. Users can confirm that “ PostgreSQL51214Database” is replaced with the “ PostgreSQLDatabase”.Įxample 5: Use REGEXP_REPLACE Function to Replace String in Table Users can verify that “ PostgreSQL51214Database” has been altered to “ 51214” using the REGEXP_REPLACE() function.Įxample 4: Use REGEXP_REPLACE Function to Remove Digitsįor removing digits, you can specify the “ :digit:” expression within the REGEXP_REPLACE function: SELECT REGEXP_REPLACE('PostgreSQL51214Database', ']','','g') The “: alpha:” expression will remove the alphabet from the existing string: To do that, the REGEXP_REPLACE() function can be used as follows: SELECT REGEXP_REPLACE('PostgreSQL51214Database', ']','','g') Suppose you have to filter the numbers from a string. Users can verify the REGEX_REPLACE() function successfully removes the extra spaces and retrieves the output as “ Welcome to database”.Įxample 3: Use REGEXP_REPLACE Function to Remove Alphabets The REGEXP_REPLACE function can also be used to remove the extra spaces: SELECT REGEXP_REPLACE('Welcome to database', '( )', ' ', 'g') The output authenticates the working of the REGEXP_REPLACE() function as it succeeded in rearranging the given strings.Įxample 2: Use REGEXP_REPLACE Function to Remove Extra Spaces \2 replaces the content of the first variable in the second place, while \1 will replace it in the first place: After that, regular expression (.*) matches the occurrence. The REGEXP_REPLACE function takes two strings: “PostgreSQL” and “Welcome” as arguments. This example will guide you on how to rearrange the strings using the REGEXP_REPLACE() function: SELECT REGEXP_REPLACE('PostgreSQL Welcome','(.*) (.*)','\2, \1') flags: refers to the argument that controls the function's behavior.Įxample 1: Use REGEXP_REPLACE Function to Arrange Name replace_str: represents a string that will be replaced in place of input_str. reg_exp: represents the regular expression pattern. input_str: the string that needs to be replaced after matching the regular expression. The description of the parameters is as follows: Syntax REGEXP_REPLACE(input_str, reg_exp, replace_str,) Let's start the journey with the syntax of the REGEXP_REPLACE function in PostgreSQL. Example 5: Use REGEXP_REPLACE Function to Replace String in Table.Example 4: Use REGEXP_REPLACE Function to Remove Digits.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |