splitting a string column correctly by spaces

Question

in my query I have several hundred records with strings from the iSeries Message Queue like this:

006 1 AccountSetBalance 0000000000 EQ 2016-03-01-18.45.42.002000 0038882665 _ 123456 12345612345678 17017362 0 0

I need to show in my results the account number part 12345678 and the balance part which is 17017362

I have tried:

SELECT MQ_Message
, SUBSTRING(MQ_Message,92,30) -- = 12345678 17017362 0 
, SUBSTRING(MQ_Message,92,8) -- = 12345678  , SUBSTRING(MQ_Message,100, CHarIndex(' ',  SUBSTRING('006 1 AccountSetBalance 0000000000 EQ 2016-03-01-18.45.42.002000 0038882665 _ 123456 12345612345678 17017362 0 0',92,20)) )
, CHarIndex(' ',  SUBSTRING('006 1 AccountSetBalance 0000000000 EQ 2016-03-01-18.45.42.002000 0038882665 _ 123456 12345612345678 17017362 0 0',99,20)) 
, CHARINDEX(' ','17017362 0 0')
    from outboundMessages WHERE message_Type = '006'

I can get the account easily enough, as the string is fixed length up to the balance, but then I need to split the string returned by SUBSTRING(MQ_Message,92,30) and get the balance part out of it which is 17017362 and will be different between 0 and maybe 999999 (in pence!)

I am really stuck trying to get the balance, having tried every possible combination of using CHARINDEX.

What is the best way to do this?

Answer 1

DECLARE @string NVARCHAR(MAX) =  '006 1 AccountSetBalance 0000000000 EQ 2016-03-01-18.45.42.002000 0038882665 _ 123456 12345612345678 17017362 0 0',
        @xml xml

select @xml = cast('<d><q>'+REPLACE(@string,' ','</q><q>')+'</q></d>' as xml)

SELECT n.v.value('q[9]','integer'),
       n.v.value('q[11]','integer')
FROM @xml.nodes('/d') AS n(v);

Result:

----------- -----------
123456      17017362

(1 row(s) affected)