How to Replace 0? [message #257655] |
Wed, 08 August 2007 23:13 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
poratips
Messages: 345 Registered: April 2005 Location: IL
|
Senior Member |
|
|
Hi,
I am having question as currently i am using REPLACE function to replace no space instead of '0' but now some of my code comes with '0' and i need to keep this zero.
current control file
LOAD DATA
INFILE '/hom/oracle/bin/jcode'
TRUNCATE
INTO TABLE jcode
TRAILING NULLCOLS
(SEQ_NUM RECNUM,
j_code POSITION(41:44) "REPLACE(:j_code, '0','')",
TITLE POSITION(11:40),
CODE POSITION(1:6)
)
Old Sample data
ADMADM1000ADMINISTRATOR ADMINISTRATION NX01
TCHADM1000TECHNICAL ADMINISTRATOR NX02
ANLADM1000ANALYST ADMINISTRATION EX03
VPSLS 1000VICE PRESIDENT SALES EX07
Loading from this file data, it wil insert NX01 as NX1 , so it was ok.
But now file comes as following:
New Sample data
ADMADM1000ADMINISTRATOR ADMINISTRATION NX01
TCHADM1000TECHNICAL ADMINISTRATOR NX02
ANLADM1000ANALYST ADMINISTRATION EX03
VPSLS 1000VICE PRESIDENT SALES EX07
VPCJZ 1000My Test FX10
Now this FX10 getting replaced as FX1 but I need to keep as FX10, how can I handle it?
Thanks,
[Updated on: Wed, 08 August 2007 23:26] by Moderator Report message to a moderator
|
|
|
Re: How to Replace 0? [message #257661 is a reply to message #257655] |
Wed, 08 August 2007 23:31 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68678 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
You could use an external table or, assuming you only want to suppress the third character if it is a '0':
DECODE(SUBSTR(:j_code,3,1),'0',SUBSTR(:j_code,1,2)||SUBSTR(:j_code,4),:j_code)
Regards
Michel
|
|
|
|