substring from varchar to number (fk)

[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

[chulett]

"2002156817161-1" isn't a number.

"2002156817161" is.
"20021568171611" is.

Would either of those work in your database?

[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

[phanee_k]

Hi,
You can use Oconv function like

OConv(Arg1,"MCN") which extracts only the numeric data from the argument passed.


cheers
Phani

[rasi]

Remember MCN only picks up the Numbers(0 to 9) not the (-).

Siva

[rasi]

The right conversion will be OConv(Arg1,"MC/A") .

If your Arg1 = HS2002156817161-1 then the result = 2002156817161-1

Thanks
Siva

[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.