| SQL Loader Syntax Help [message #206088] |
Tue, 28 November 2006 11:49 |
tbone231
Messages: 2
Registered: March 2006
|
Junior Member |
|
|
Hello
I am receiving a values like this in a file I am attempting to SQL Load.
"F123" or "F12340" or "F.9000"
What I want to be able to do is always set the decimal after the fourth position, unless the entire string is only four characters long, and then there would be no decimal, so the values would end up looking like this:
"F123" or "F123.40" or "F900.0"
To accomplish this, I'd think I'd want to first strip out all the non-alpha numeric values from the string, then count the string and if it is greater than 4 total characters, add the decimal to be after the fourth position.
I know how to the replace, but and I understand the concept behind what I need to do, but I'm not sure how to do the appropriate check in SQL loader to see if the value is 4 characters and then if it is, add the decimal to the appropriate spot and if is not 4 characters to not add a decimal at all.
It seems this should be easy, but I just need a bit of help.
Here is what I have so far, with the replace of the decimal:
OPTIONS
(
SILENT=(HEADER, FEEDBACK)
BINDSIZE=100000
)
load data
infile "f_values.txt"
BADFILE 'f_values_bad.log'
DISCARDFILE 'f_values_discard.log'
into table F_VALUES
append
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
F_VALUE POSITION(1) CHAR(100) "REPLACE:(F_VALUE, '. ')"
NAME_FIRST CHAR(100),
NAME_LAST CHAR(100)
)
And here is an example of the file, "f_values.txt":
"F123", "SMITH", "JOHN"
"F12340", "JONES", "SAM"
"F900.0", "EDWARDS", "NANCY"
I'd just like to understand how to write the rest of the command to insert the decimal into the proper position for the "F_VALUE".
Any help is appreciated!
Thanks!
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
