OraFAQ Forum: Server Utilities » How to trim spaces between strings in SQL Loader

Home » RDBMS Server » Server Utilities » How to trim spaces between strings in SQL Loader

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

How to trim spaces between strings in SQL Loader [message #266336]

Mon, 10 September 2007 10:34

kvignes1
Messages: 33
Registered: September 2007

Member

Hi
Need to know how to trim spaces between strings in SQL Loader while loading. We are using "position" to load data from flat file.(*.DAT)

Example
If I am gettin a value like this, image position(1:36).. this has to go to a staging table column "Name". I know I can use "trim" function which will trim right/left side. But how can i trim in the middle?? any advice pls.

'        STEVE           ROB         '

I want this value to store in a column like

'STEVE ROB'

is it possible in SQL Loader???

Pls advice.

[EDITED by LF - added [code] tags to improve readability and emphasize original poster's idea]

[Updated on: Mon, 10 September 2007 12:03] by Moderator

Report message to a moderator

Re: How to trim spaces between strings in SQL Loader [message #266343 is a reply to message #266336]

Mon, 10 September 2007 10:53

Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

how can i trim in the middle??

Quote:

I want this value to store in a column like 'STEVE ROB'

How is this "trimmed in the middle"?

Regards
Michel

Report message to a moderator

Re: How to trim spaces between strings in SQL Loader [message #266391 is a reply to message #266343]

Mon, 10 September 2007 13:18

kvignes1
Messages: 33
Registered: September 2007

Member

Dont know , my client want to trim the spaces between those two names and populate it in a column... This has to be done while loading that data through SQL Loader.

Is there anyway, i can use regular expression to do it. I hope regular expression are available from oracle 10g. First of all is it possible to do in anyway??

[Updated on: Mon, 10 September 2007 13:20]

Report message to a moderator

Re: How to trim spaces between strings in SQL Loader [message #266394 is a reply to message #266391]

Mon, 10 September 2007 13:23

Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

This is possible but your example does not show that you trim middle space.
You can use TRANSLATE function to remove all spaces.

Regards
Michel

[Updated on: Mon, 10 September 2007 13:23]

Report message to a moderator

Re: How to trim spaces between strings in SQL Loader [message #266407 is a reply to message #266336]

Mon, 10 September 2007 15:01

kvignes1
Messages: 33
Registered: September 2007

Member

Thanks.
I am not sure whether TRANSLATE will help me. But I guess REPLACE will help me to some level. But when I use REPLACE, I am losing all Spaces b/w strings. Any idea how to retain single space b/w name??? Also Pl let me know how TRANSLATE help me to do it.if possible???

FYI, I may get data like this in the flat file POSITION (1:36)
Consider '_' as 'blank space'

'____________________STEVE________ROB_________'

'___________LEE____________LIANG______________'

So both records should be inserted into the staging table column as
'STEVE ROB'
'LEE LIANG'

when I use REPLACE, I am getting like this
'STEVEROB'
'LEELIANG'

Query:
SQL> Select replace(TRIM(' STEVE ROB '),' ','') FROM DUAL;

REPLACE(
--------
STEVEROB

[Updated on: Mon, 10 September 2007 15:05]

Report message to a moderator

Re: How to trim spaces between strings in SQL Loader [message #266408 is a reply to message #266407]

Mon, 10 September 2007 15:16

Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You should have been clearer in the first post.
Have a look at: Replacing Multiple Blanks by single blanks in text

Regards
Michel

Report message to a moderator

Re: How to trim spaces between strings in SQL Loader [message #266418 is a reply to message #266407]

Mon, 10 September 2007 17:28

ebrian
Messages: 2794
Registered: April 2006

Senior Member

You could specify the following in your control file:

"trim(regexp_replace(:<column_name>, '[ ]{2,}', ' '))"

Report message to a moderator

Previous Topic:	tablespace import hangs
Next Topic:	Size rquired for Export and Import of database

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jun 22 22:34:24 CDT 2024