The SMS Puzzle

Consider the following SMS:

Rafał's SMS Test - This is a sample SMS using my name 
to shorten the length of single SMS parts for better clarity. 

Please note in what order the messages will appear 
in the _SMSMessageTracking data view that we will be 
using to stitch the SMS back together 🤞

What looks like a message with a reasonable length will actually be sent as 4 separate messages.

Build the ID

Query

Query - Step 1

Select
    *,
    Concat(SMSJobID,'>',SMSBatchID,'>',SubscriberID) as CompositeKey
From
    _SMSMessageTracking
Where
    CreateDateTime >= DATEADD(hour,-125, GETDATE())

Query - Step 2

Select
    a.*,
    LEFT(b.FullMessageText, LEN(b.FullMessageText) - 1) as Debug,
    REPLACE(REPLACE(REPLACE(LEFT(b.FullMessageText, LEN(b.FullMessageText) - 1), '⮒', ''), '~', ''), 'Ω', '') as FullMessageText,
    b.MessageParts,
    Len(b.FullMessageText) as MessageLength
From
    ENT.[SMSMessageTracking with Ordinals and JobIDs - Step 1] a
Join
    (
        Select
            CompositeKey,
            Count(CompositeKey) as MessageParts,
            STUFF((
                SELECT 
                    REPLACE(REPLACE(CAST(MessageText AS NVARCHAR(MAX)) + 'Ω', CHAR(13), '⮒'), CHAR(10), '~')
                From
                    ENT.[SMSMessageTracking with Ordinals and JobIDs - Step 1] a
                Where
                    a.CompositeKey = s.CompositeKey
                Order by
                    CompositeKey asc, Ordinal asc
                For XML PATH(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)')
        ,1,0,'') AS FullMessageText
        From
            ENT.[SMSMessageTracking with Ordinals and JobIDs - Step 1] s
        Group by
            s.CompositeKey
    ) b
On  
    a.CompositeKey = b.CompositeKey
Where 
    a.Ordinal = 0