Home » RDBMS Server » Server Utilities » How to trim spaces between strings in SQL Loader
How to trim spaces between strings in SQL Loader [message #266336] Mon, 10 September 2007 10:34 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
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
Re: How to trim spaces between strings in SQL Loader [message #266391 is a reply to message #266343] Mon, 10 September 2007 13:18 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
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
Re: How to trim spaces between strings in SQL Loader [message #266418 is a reply to message #266407] Mon, 10 September 2007 17:28 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
You could specify the following in your control file:

"trim(regexp_replace(:<column_name>, '[ ]{2,}', ' '))"
Previous Topic: tablespace import hangs
Next Topic: Size rquired for Export and Import of database
Goto Forum:
  


Current Time: Fri Dec 09 04:07:56 CST 2016

Total time taken to generate the page: 0.06826 seconds