This content has been marked as final. Show 5 replies
Here is a code snippet that changes the input string from '82.20-' to '-82.20' and so forth [ if there is a minus sign at the end of the string - otherwise it leaves it as-is]:
So, if the column name is "num_field" (number field), you can try something like this in your control file:
with data1 as (select '82.20-' num from dual union all select '881.02-' from dual union all select '2016.02' from dual) select decode(substr(num, -1), '-', '-'||substr(num,0, length(num)-1), num) num_field from data1 NUM_FIELD -82.20 -881.02 2016.02
Let me know if it worked or not. I don't have access to Oracle environment so I can't try it myself.
LOAD DATA INFILE ... BADFILE ... APPEND INTO TABLE ... FIELDS TERMINATED BY ... ( ..., ..., num_field "decode(substr(:num_field, -1), '-', '-'||substr(:num_field,0, length(:num_field)-1), :num_field)" ..., )
848265 wrote:Do you mean some have sign at the end and some in the beginning or sign, if specified, always appears at the end? If latter use to_char:
In the data file, there are some values containing minus at the end of the value
field_name "TO_NUMBER(:field_name, '9999.99S')"
SQL> select to_number('881.02-','9999.99S') from dual 2 / TO_NUMBER('881.02-','9999.99S') ------------------------------- -881.02 SQL>
When there is a negative number, the minus sign will appear at the end of the number, whereas a positive number will not have any specific signs and will look like a normal number.
select to_number('881.02-','9999.99S') from dual fails when it is a positive number. The positive number would have nothing, it will be a kind of how a normal number looks like.
I tried, S, MI but they fails on normal scenarios.