Re: Funtion to clean up strings?

On Tuesday 17 Feb 2009, Andreas wrote:
> [snip]
> case
>   when regexp_replace(p, E'[^0-9+]', '', 'g') ~ E'^(\\+|00)49'
>     then '0'||
>        regexp_replace(
>           regexp_replace(
>              regexp_replace(p, E'[^0-9+()]', '', 'g')
>           , '\\(0\\)||\\(||\\)', '', 'g')
>        , E'^(?:\\+|00)49(.*)', E'\\1')
>   when regexp_replace(p, E'[^0-9+]', '', 'g') ~ E'^(\\+|00)'
>     then '+'||
>        regexp_replace(
>              regexp_replace(
>                 regexp_replace(p, E'[^0-9+()]', '', 'g')
>              , '\\(0\\)||\\(||\\)', '', 'g')
>        , E'^(?:\\+||00)(.*)', E'\\1')
>   else
>     regexp_replace(p, E'[^0-9]', '', 'g')
> end
>
> That would catch the leading spaces in "  00 49 ( 0 ) 1 2 3 456 -0",
> too. Creating a sql-function thows a WARNING:  nonstandard use of \\
> in a string literal
> but it still works. Do you know a better or more correct way to reach
> the same?
>
> Perhaps one could find a way with less calls to regexp_replace ?

That is what I would have tried too :)  The only improvement I can think 
of is to replace one instance of regex_replace with a string replace, 
since the string (0) is fixed.

On the other hand, I'm not an expert at Pg functions by any means, so 
someone else may have a faster or more elegant solution.

Regards,

-- Raju
-- 
Raj Mathur                raju@kandalaya.org      http://kandalaya.org/      GPG: 78D4 FC67 367F 40E2 0DD5  0FEF C968
D0EFCC68 D17F
 
PsyTrance & Chill: http://schizoid.in/   ||   It is the mind that moves