I have a whole SWIFT message with fees in one "cell" as SwiftMessage.Body. So the whole message is in one string. What I need to do is, extract certain data from it. Using SSRS and MS SQL The message looks something like this:
.....(FEE 1)
:20C::PCOM//C22033100734330
:20C::PREF//FC22033100734330
:22H::PNTP//SEFP
:24B::ACTV//NEWP
:19A::AMCO//NEUR50
:99A::DAAC//001
.....(FEE 2)
:20C::PCOM//C22033100734331
:20C::PREF//FC22033100734331
:22H::PNTP//SEFP
:24B::ACTV//NEWP
:19A::AMCO//EUR40
:99A::DAAC//002
.....(FEE n)
there can be any number of fees, not just 2
The result should be extracting :20C::PREF// and :19A::AMCO//:
| ID | Amount |
| ------------ | ------------ |
| FC22033100734330 | -50 |
| FC22033100734331 | 40 |
what I have right now
SELECT
SUBSTRING(swf.SwiftMessage.Body, (CHARINDEX(':20C::PREF', swf.SwiftMessage.Body) + 12)
, CHARINDEX(':22H::PNTP',swf.SwiftMessage.Body) - CHARINDEX(':20C::PREF', swf.SwiftMessage.Body) - 12) as REF1,
SUBSTRING(swf.SwiftMessage.Body, (CHARINDEX(':19A::AMCO', swf.SwiftMessage.Body) + 12)
, CHARINDEX(':99A::DAAC',swf.SwiftMessage.Body) - CHARINDEX(':19A::AMCO', swf.SwiftMessage.Body) - 12) as AMT
FROM
swf.SwiftMessage
so with this I am somehow able to extract data I need (the amount is in format currency+amt which I can deal with later hopefuly). The main problem right now is how to deal with the fact, that there might be more fees, than just one so I need to make some kind of a loop? that will go through the whole string and find every :20C::PREF// and :19A::AMCO// values.
3条答案
按热度按时间8ulbf1ek1#
First of all, I would suggest doing this splitting at a different place, either when inserting the data into the database, or after retrieving it, perhaps using the language that the program used to communicate with the DB.
If you really must do the splitting using SQL alone, you have a few options to loop over the Data. They either use Recursion, classic
WHILE
loops or use theSTRING_SPLIT
function, as mentioned in this post .If you happen to use SQL Server 2016 or later, I suggest you use
STRING_SPLIT
, something along the lines ofthis together with
CROSS APPLY
whould get you a whole step closer to parsing the data for each message:then just substring each value
9rygscc12#
Here is an option which parses the string via JSON to maintain the SEQUENCE. Then it becomes a small matter of a conditional aggregation
Note: this is keyed on EUR. If you have other currencies, it would be a small tweak
Example
Results
zaq34kh63#
Give this a try:
This shouldn't be reliant on the values being in any specific format or type, just that they are referenced as
:19A::
and:20C::