Andreas escribió: > Hi, > I've got a similar issue with a function that uses regular-expression-magic. > I got it from the sql list and it works but I'm just about 75% aware of how. > Still PG complains about those \\ everywhere. > > Replacing every \ by || E'\\' || would make it ... cough ... not > looking cuter as it allready is. > What would be the correct syntax here? Just prepend E to the whole string, i.e. instead of '\\(0\\)||\\(||\\)' use E'\\(0\\)||\\(||\\)' If you're list most people and hate having to double the \s use dollar-quoting, as shown below. BTW it seems the function could be written in the SQL language instead of plpgsql, which could make it faster. > CREATE OR REPLACE FUNCTION cleanphonenr(text) > RETURNS text AS > $BODY$ > BEGIN > RETURN CASE > WHEN regexp_replace($1, '[^0-9+]', '', 'g') ~ $$^(\+|00)49$$ > THEN '0'|| > regexp_replace( > regexp_replace( > regexp_replace($1, '[^0-9+()]', '', 'g') > , $$\(0\)||\(||\)$$, '', 'g') > , $$^(?:\+|00)49(.*)$$, $$\1$$) > WHEN regexp_replace($1, '[^0-9+]', '', 'g') ~ $$^(\\+|00)$$ > THEN '+'|| > regexp_replace( > regexp_replace( > regexp_replace($1, '[^0-9+()]', '', 'g') > , $$\(0\)||\(||\)$$, '', 'g') > , $$^(?:\+||00)(.*)$$, $$\1$$) > ELSE > regexp_replace($1, E'[^0-9]', '', 'g') > END; > END; > $BODY$ > LANGUAGE 'plpgsql' VOLATILE > COST 100; -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
