OraFAQ Forum: Server Utilities » How to Replace 0?

Home » RDBMS Server » Server Utilities » How to Replace 0?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to Replace 0? [message #257655]

Wed, 08 August 2007 23:13

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

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: How to Replace 0? [message #257916 is a reply to message #257661]

Thu, 09 August 2007 10:37

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

Report message to a moderator

Previous Topic:	import
Next Topic:	sqlldr errors

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 03 11:32:13 CST 2024