| 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.
I want this value to store in a column like
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 #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 |
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
