string_replace would only replace one searchstring at a time.
In this case I need to replace 3 : (0) ( )
because there could be some braces not just as (0) since the innermost
replace spares braces regardless where they are.
Could one express the following in one expression
1) remove all spaces
2) remove everything but 0-9+ or the first occurance (0)
Leading spaces had to be removed anyway because the distract the CASE
... WHEN when it checks for "+49..." but gets " +49...".
Then I could use string_replace at the end.
Do you know the correct way to code the backslashes to avoid the Warning?
Regards
Andreas
Raj Mathur schrieb:
> 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
>