mtrussell
asked on
Include Txt to a certain character in a string
I have the following EDI message and have the following statement in a query;
Units: Mid([swift],InStr(1,[swift ],":36B::S ETT//UNIT/ ")+17,12)
In this particular line, the number of characters is variable until the ','. So what I need to do is bring in 3493 in this case but if it was 99999, then I would need to bring in 99999 and so forth. It is whatever value from the / to the ,.
The above code is bring in characters from the next line. How do you stop at the ','?
I was trying the charindex function but without much luck. Any ideas?
{1:F01MIDLGB2992297029}{2: I543ESSENO KXXN}{3:{1 08:113137} }{4:
:16R:GENL
:20C::SEME//45029
:23G:NEWM
:16S:GENL
:16R:TRADDET
:98A::TRAD//20111110
:98A::SETT//20111115
:35B:ISIN NO0010985
STAL ASA
:16S:TRDET
:16R:FAC
:36B::SETT//UNIT/3493,
:97A::SAFE//097002601
:16S:FIAC
:16R:SETDET
:22F::SETR//TRAD
:16R:SETPRTY
:95P::REAG//SENOKXXXX
:16S:SETPRTY
:16R:SETPRTY
:95P::RECU//EMNL2A
:97A::SAFE//970736
:16S:SETPRTY
:16R:SETPRTY
:95P::BUYR//RBFRPP
:16S:SETPRTY
:16R:SETPRTY
:95P::PSET//VPOKK
:16S:SETPRTY
:16R:AMT
:19A::SETT//NOK5869,15
:16S:AMT
:16S:SETDET
-}{5:{CHK:6EC4F18}}
Units: Mid([swift],InStr(1,[swift
In this particular line, the number of characters is variable until the ','. So what I need to do is bring in 3493 in this case but if it was 99999, then I would need to bring in 99999 and so forth. It is whatever value from the / to the ,.
The above code is bring in characters from the next line. How do you stop at the ','?
I was trying the charindex function but without much luck. Any ideas?
{1:F01MIDLGB2992297029}{2:
:16R:GENL
:20C::SEME//45029
:23G:NEWM
:16S:GENL
:16R:TRADDET
:98A::TRAD//20111110
:98A::SETT//20111115
:35B:ISIN NO0010985
STAL ASA
:16S:TRDET
:16R:FAC
:36B::SETT//UNIT/3493,
:97A::SAFE//097002601
:16S:FIAC
:16R:SETDET
:22F::SETR//TRAD
:16R:SETPRTY
:95P::REAG//SENOKXXXX
:16S:SETPRTY
:16R:SETPRTY
:95P::RECU//EMNL2A
:97A::SAFE//970736
:16S:SETPRTY
:16R:SETPRTY
:95P::BUYR//RBFRPP
:16S:SETPRTY
:16R:SETPRTY
:95P::PSET//VPOKK
:16S:SETPRTY
:16R:AMT
:19A::SETT//NOK5869,15
:16S:AMT
:16S:SETDET
-}{5:{CHK:6EC4F18}}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
BTW, I'm assuming that [swift] if the field that contains your data above.
ASKER
Thanks