Re: REGEXP_REPLACE doesn't give the desired output

"Jure" <jure.br_at_gmail.com> wrote in message news:1150792691.422827.242940_at_h76g2000cwa.googlegroups.com...
> Hi,
>
> i have a problem using REGEXP_REPLACE, since it doesn't make any change
> on input data. The interesting thing is that on an DB istance which has
> different language parameters, it works. Let me explain more in detail.
> I'd like to replace all nondigit characters with blanks within a
> cetrain string. The result of the following SQL should be self
> explanatory:
>
> SELECT SYSTIMESTAMP original, REGEXP_REPLACE(SYSTIMESTAMP, '\D', '')
> converted
> FROM dual
>
> and the result is:
> ORIGINAL|CONVERTED
> 20.6.2006 10:23:38,927000 +02:00|2006061023389270000200
>
> So from '20.6.2006 10:23:38,927000 +02:00', I got the string
> '2006061023389270000200'.
>
> This works on database A, but doesn't work on database B (the input to
> REGEXP_REPLACE is equal to its output). I ran the following query to
> find out the differences between these two DBs (I hope this information
> is useful, since I don't know where else to look....I'm quite new to
> Oracle):
>
> SELECT DB_A.NAME, DB_A.COMMENT$, DB_A.VALUE$ value_DB_A, DB_B.VALUE$
> value_DB_B
> FROM sys.props$ DB_A, sys.props$@link_to_B DB_B
> WHERE DB_A.NAME = DB_B.NAME
> AND DB_A.VALUE$ <> DB_B.VALUE$
>
> and the result:
> NAME|COMMENT$|VALUE_DB_A|VALUE_DB_B
> NLS_CHARACTERSET|Character set|EE8MSWIN1250|EE8ISO8859P2
> NLS_RDBMS_VERSION|RDBMS version for NLS
> parameters|10.2.0.1.0|10.1.0.3.0
> GLOBAL_DB_NAME|Global database name|DBA|DBB
>
>
> I also tried hardcoding the string passed to REGEXP_REPLACE, but it
> didn't work.
>
> Thank you in advance for any suggestion.
>

Not sure if this helps but systimestamp is not a string it is a time stamp. (a type of date). You are relying on a default nls_date parameter which could be different in two different databases. Use to_char(systimestamp,format string) (eg to_char(systimestamp,'mm/dd/yyyy hh24:mi:ss')

Jim Received on Tue Jun 20 2006 - 08:41:39 CDT