Page 1 of 1
substring from varchar to number (fk)
Posted: Tue Aug 03, 2004 10:33 am
by bobby
HI,
i have source varchar2(20) like HS2002156817161-1 AND MY TARGET IS
Number(20)(fk) it should be 2002156817161-1 how can i do ir and my target is fk is that conversation possible. pl advise.
Thanks,
Bobby
Posted: Tue Aug 03, 2004 11:08 am
by chulett
"2002156817161-1" isn't a number.
"2002156817161" is.
"20021568171611" is.
Would either of those work in your database?
Posted: Tue Aug 03, 2004 11:47 am
by ketfos
Hi,
One way is use the routine given below
Ans = Convert(Arg1, "", Arg2)
Where Arg1 is your string of characters you wish to remove in output
Arg2 is the input string
e.g
Arg1 = abcdefshijklmnopqrstuvwxy
Arg2 = HS2002156817161-1
Hope this help.
Ketfos
Posted: Tue Aug 03, 2004 9:13 pm
by phanee_k
Hi,
You can use Oconv function like
OConv(Arg1,"MCN") which extracts only the numeric data from the argument passed.
cheers
Phani
Posted: Tue Aug 03, 2004 10:08 pm
by rasi
Remember MCN only picks up the Numbers(0 to 9) not the (-).
Siva
Posted: Tue Aug 03, 2004 10:12 pm
by rasi
The right conversion will be OConv(Arg1,"MC/A") .
If your Arg1 = HS2002156817161-1 then the result = 2002156817161-1
Thanks
Siva
Posted: Tue Aug 03, 2004 10:27 pm
by chulett
The whole point is to get down to something that will load into a numeric field. That makes the 'right' conversion MCN in this particular case because the dash would need to come out. Note that the
DIGITS transform (which would make it a little more obvious what is going on) uses the OConv function with MCN.
I don't think the issue is so much the
how as it is the
what. Bobby needs to explain what an "fk" (foreign key reference) has to do with his question. You can't create a foreign key relationship using only part of a field, so unless his source isn't directly involved in the fk relationship, I don't see how it will work.
If it will work for his situation, then at least he's got several means to his ends now.