Home » RDBMS Server » Server Utilities » How to Replace 0?
How to Replace 0? [message #257655] Wed, 08 August 2007 23:13 Go to next message
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 messageGo to next message
Michel Cadot
Messages: 64102
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
Re: How to Replace 0? [message #257916 is a reply to message #257661] Thu, 09 August 2007 10:37 Go to previous message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks, I don't want to use as EXT table as it will used by application user.
I will try your DECODE.

Thanks
Previous Topic: import
Next Topic: sqlldr errors
Goto Forum:
  


Current Time: Fri Dec 02 13:59:33 CST 2016

Total time taken to generate the page: 0.10378 seconds