Regular expression replace in PL/pgSQL

Posted by dreamlax on Stack Overflow See other posts from Stack Overflow or by dreamlax
Published on 2010-05-03T05:23:58Z Indexed on 2010/05/03 5:28 UTC
Read the original article Hit count: 424

Filed under:

plpgsql

|

regex

|

trigger

|

postgresql

If I have the following input (excluding quotes):

"The ancestral   territorial      imperatives of the    trumpeter swan"

How can I collapse all multiple spaces to a single space so that the input is transformed to:

"The ancestral territorial imperatives of the trumpeter swan"

This is going to be used in a trigger function on insert/update (which already trims leading/trailing spaces). Currently, it raises an exception if the input contains multiple adjacent spaces, but I would rather it simply transforms it into something valid before inserting.

What is the best approach? I can't seem to find a regular-expression replace function for PL/pgSQL. There is a text_replace function, but this will only collapse at most two spaces down to one (meaning three consecutive spaces will collapse to two). Calling this function over and over is not ideal.

© Stack Overflow or respective owner

Related posts about plpgsql

plpgsql function to generate random readable strings

as seen on Stack Overflow - Search for 'Stack Overflow'
Hi I have written the following function but it's isn't returning anything when I run it. Can somebody help identify the issue? CREATE OR REPLACE FUNCTION GenerateReadableRandomString ( len INT ) RETURNS varchar AS $$ DECLARE validchars VARCHAR; randomstr VARCHAR; randint INT; … >>> More
PLPGSQL array assignment not working, "array subscript in assignment must not be null"

as seen on Stack Overflow - Search for 'Stack Overflow'
Hello there, When assigning mobilenumbers to a varchar[] in a loop through results it gives me the following error: "array subscript in assignment must not be null" Also, i think the query that joins member uuids, and group member uuids, into one, grouped on the user_id, i think it can be done… >>> More
PLPGSQL : Return a record from function executed by INSERT/UPDATE rule

as seen on Stack Overflow - Search for 'Stack Overflow'
Do the following scheme for my database: create sequence data_sequence; create table data_table { id integer primary key; field varchar(100); }; create view data_view as select id, field from data_table; create function data_insert(_new data_view) returns data_view as $$declare _id… >>> More
Stored insert procedure in plpgsql

as seen on Stack Overflow - Search for 'Stack Overflow'
I want to do something like this in PostgreSQL. I tried this: CREATE or replace FUNCTION create_patient(_name text, _email text, _phone text, _password text, _field1 text, _field2 text, _field3 timestamp, _field4 text, OUT _pid integer, OUT _id integer) RETURNS record AS $$ DECLARE _id… >>> More
Développer des fonctions scalaires (UDF) avec PLpgSQL, par SQLpro

as seen on Developper.com - Search for 'Developper.com'
Bonjour, voici le premier d'une nouvelle série d'articles consacrés au développement des fonctions sous PLpgSQL Ce premier article est consacré aux fonctions scalaires appelées dans la norme SQL "UDF" pour User Defined Function Il sera suivi dans les mois prochains de deux autres… >>> More

Related posts about regex

Find multiple regex in each line and skip result if one of the regex doesn't match

as seen on Stack Overflow - Search for 'Stack Overflow'
I have a list of variables: variables = ['VariableA', 'VariableB','VariableC'] which I'm going to search for, line by line ifile = open("temp.txt",'r') d = {} match = zeros(len(variables)) for line in ifile: emptyCells=0 for i in range(len(variables)): regex = r'('+variables[i]+r')[:|=|\(](-… >>> More
OWASP Regex Repository: Is this regex correct?

as seen on Stack Overflow - Search for 'Stack Overflow'
I was looking at the regular expression for validating various data types from the (OWASP Regex Repository). One of the regular expressions in there is called safetext and looks like: ^[a-zA-Z0-9\s.\-]+$ My first question is: Is this regular expression correct? complementary question If this… >>> More
Make a Perl-style regex interpreter behave like a basic or extended regex interpreter

as seen on Stack Overflow - Search for 'Stack Overflow'
I am writing a tool to help students learn regular expressions. I will probably be writing it in Java. The idea is this: the student types in a regular expression and the tool shows which parts of a text will get matched by the regex. Simple enough. But I want to support several different regex… >>> More
JS regex isn't matching, even thought it works with a regex tester

as seen on Stack Overflow - Search for 'Stack Overflow'
I'm writing a piece of client-side javascript code that takes a function and finds the derivative of it, however, the regex that's supposed to match with the power rule fails to work in the context of the javascript program, even though it sucessfully matches when it's used with an independent regex… >>> More
c# RegEx with "|"

as seen on Stack Overflow - Search for 'Stack Overflow'
I need to be able to check for a pattern with | in them. For example an expression like d*|*t should return true for a string like "dtest|test". I'm no regex hero so I just tried a couple of things, like: Regex Pattern = new Regex("s*\|*d"); //unable to build because of single backslash Regex Pattern… >>> More