This content has been marked as final. Show 7 replies
select to_number(substr(value1,69,10)) from test;
wht is the data stored in value column???
You have to check your nls environment on both servers, most likeley
and take care, that your settings are correct in regard to your decimal/thousand delimiter.
select value from nls_session_parameters where parameter = 'NLS_NUMERIC_CHARACTERS'
Misread the question,
you can try to set the proper nls_numeric_characters explizitly
Edited by: Maxim Demenko on Jan 20, 2011 8:56 AM
alter session set nls_numeric_characters='.,';
So the conversion does not fail with the data and row you think - it is failing somewhere else. And as you are showing a SQL select, quite likely that failure is with another row where that substr() does not return a string that can be converted to numeric.
SQL> select to_number('00000000.1') as N from dual; N ---------- .1 SQL>
datatype of column value1 is varchar2(204)
The trouble comes from the dot int the number varchar2.
try this :
select to_number(replace(substr(value1,69,10), '.',',')) from dual
Check your environment variable NLS_NUMERIC_CHARACTERS
where parameter = 'NLS_NUMERIC_CHARACTERS'
gives this ,.