Did Sean want something else
Peter, May 29, 2003 - 9:19 am UTC
Tom, I always seem to learn something here.
Didn't Sean also have a second part to the question where he is trying to split the end of the text string into another column?
I guess that we would need an extra column(d) in table T and an extra d position(36:60) for the righthand end of the string - can you use nvl in the control function to allow for the many instance of NULL in sean's input file?
May 29, 2003 - 9:50 am UTC
doh! you are right, missed that. sorry:
ops$tkyte@ORA817DEV> create table t ( a varchar2(30), b date, c varchar2(20), d varchar2(20) );
Table created.
ops$tkyte@ORA817DEV> !cat t.ctl
load data
INFILE *
INTO TABLE t
replace
FIELDS TERMINATED BY ','
(a position(3:17),
b position(18:34) date "mmddyyyy hh24:mi:ss",
c position(36:60) "substr(:c,1,instr(:c,'/')-1)",
d position(1:1) "decode(instr(:c,' '),0,'',substr(:c,instr(:c,' ')+1))"
)
begindata
aadummy 05222001 10:01:25 NNS/ORACLE
abdummy 05222001 14:45:09 QIP_NNS/ORACLE
acdummy 06132001 09:36:56 NORTHERN_2000/ORACLE
ahl291b 06121997 14:28:25 EO_CENTRAL_GRABEN/ORACLE
akerq20 07052002 00:00:00 EO_NSRT/ORACLE
alba99 10092002 11:15:00 EO_NSRT/ORACLE
ang_test 05222003 11:37:08 LOADSEIS/ORACLE master_2d
arbroath 04171996 16:47:40 EO_NSRT/ORACLE
auk97 03231998 14:44:05 CENTRAL97/ORACLE
auk98 05201998 08:00:00 CENTRAL97/ORACLE
auktemp 11282002 12:19:08 CENTRAL97/ORACLE
auktr43d 04022003 09:54:30 EO_W_OF_SHETLANDS/ORACLE
ops$tkyte@ORA817DEV> !sqlldr / t
SQL*Loader: Release 8.1.7.4.0 - Production on Thu May 29 09:49:27 2003
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Commit point reached - logical record count 12
ops$tkyte@ORA817DEV> select * from t;
A B C D
------------------------------ ----------- -------------------- --------------------
aadummy 22-MAY-2001 NNS
abdummy 22-MAY-2001 QIP_NNS
acdummy 13-JUN-2001 NORTHERN_2000
ahl291b 12-JUN-1997 EO_CENTRAL_GRABEN
akerq20 05-JUL-2002 EO_NSRT
alba99 09-OCT-2002 EO_NSRT
ang_test 22-MAY-2003 LOADSEIS master_2d
arbroath 17-APR-1996 EO_NSRT
auk97 23-MAR-1998 CENTRAL97
auk98 20-MAY-1998 CENTRAL97
auktemp 28-NOV-2002 CENTRAL97
auktr43d 02-APR-2003 EO_W_OF_SHETLANDS
12 rows selected.
fixed that up....
Cutting the end off a string
Sean Hoyland, May 29, 2003 - 10:02 am UTC
Thanks Tom
Very helpful. I'm actually bringing the data in via the UTL_FILE package but everything I needed to know is in your response and I learnt a lot to boot.
Get website!
Sean
Oh that's neat
Peter, May 29, 2003 - 10:07 am UTC
I didn't think about using c: twice -- I would have read the field again!
Guess that's why you have your job and I have mine ;-)
two :c meanings
A reader, May 29, 2003 - 11:57 am UTC
Hi, TOm,
The first :c i know, but where does the second :c starts from, and position(1:1) means only one character?
Please clarify,
Thanks
May 29, 2003 - 1:44 pm UTC
the second :c is the first :c -- it is just a bind variable.
position (1:1) is just a placeholder -- I'm deriving the value of D from :c entirely so it didn't matter if I used postition(1:1000) or position(1:1)
in sqlldr control files, you are just defining bind variables -- I have bind variables :A, :B, :C, and :D
In the absence of a "sqlfunction" in the ctl file, oracle would have generated:
insert into t ( a,b, c, d ) values (:a,:b,:c,:d);
since I had sql functions in there it generated:
insert into t ( a,b,c,d ) values
(:a,
to_date(:b,'mmddyyyy hh24:mi:ss'),
substr(:c,1,instr(:c,'/')-1),
decode(instr(:c,' '),0,'',substr(:c,instr(:c,' ')+1))
);
instead and bound the :A, :B ,and :C binds to it (ignoring :d, never used it)
A reader, May 29, 2003 - 2:35 pm UTC
Cool!!!!!
Excellent!!!!!
gs, May 29, 2003 - 3:52 pm UTC
Great Answer Tom. Learned so much from your answer(s).
finding substring in a string
Florin, July 24, 2007 - 5:24 am UTC
Hi Tom,
I have a string varchar(4000) containing also chars and binary (tha table has million of records).
Somewhere in the middle of the string (not always in the same position) I have a number in the format like: 123.1
I need to extract only this 5 characters (4 digits + the point). I know the point is the first appearance from the end of the string. So basically , I need to find the point, then 3 digits before the point and one digit after the point.
The string looks like:
64363179 CI 0 EUR 0464363179 CI DEF N A 13 02 123.1 - 12 08 D ÿÿÿÿÿìÿÿ
... plus a lot of binary data inside.
So I have to extract only the "123.1" .
Many many thanks in advance!
Regards,
Florin
July 24, 2007 - 9:50 am UTC
ops$tkyte%ORA9IR2> select substr( data, instr( data, '.' )-3, 5 )
2 from (
3 select '64363179 CI 0 EUR 0464363179 CI DEF N A 13 02 123.1 DAFDSD FADAF ' data from dual
4 )
5 /
SUBST
-----
123.1
neat
Jay, July 24, 2007 - 10:07 am UTC
Very nice Tom. Thanks as always!
Jay
Cool
Mital, July 24, 2007 - 10:10 am UTC
That was very informative Tom.
I learned something more about Oracle in-built functions on your site.
But...
dfg, July 24, 2007 - 1:20 pm UTC
Depending on how you read the Florin's statement,
"I know the point is the first appearance from the end of the string",
you might need to add a third argument (-1) to the instr function to have it start searching from the end of the string.
... from the end of the string
Florin, July 25, 2007 - 3:48 am UTC
Thanks a lot!
But I need to find the point from the end of the string.
Someone suggested here to add the -1 as an argument. Would please show me this based on my example?
Regards,
Florin
July 26, 2007 - 9:10 am UTC
instr is sort of, well, fully documented.
suggestion: you see the technique - you need to find the point where the decimal is, then everything else is easy.
so, read the documentation on instr - you have so many hints as to how to do this already!!!
Search from the end backwards
John, July 25, 2007 - 5:30 am UTC
Floris, simply change instr(data, '.') to instr(data, '.'
, -1).
ops$tkyte%ORA9IR2> select substr( data, instr( data, '.'<b>, -1</b> )-3, 5 )
2 from (
3 select '64363179 CI 0 EUR 0464363179 CI DEF N A 13 02 123.1 DAFDSD FADAF ' data from dual
4 )
5 /
HTML error
John, July 25, 2007 - 5:32 am UTC
Offcourse, you should ignore the and tags in my example above. I meant to emphasise the argument by making it bold...