I've just debugged it and theres a fault in the original CTE not parsing the last value in the list.

so I've 'tweaked' it to add on a terminator, such that it will pick up the value for X, without impacting the rest of the query.

DECLARE @CHARACTERS TABLE (CHARS VARCHAR(2))

INSERT INTO @CHARACTERS VALUES

('N'), ('E'), ('M'), ('H'), ('T'), ('V'), ('L'), ('C'), ('XX');

DECLARE @STRING VARCHAR(500);

SET @STRING= '2449.555N06704.2855EM0701H071T44.098V11.764L0.372C1'+'XX';

DECLARE @visid int=1,@msginID int=2

DECLARE @len int;

SET @len =LEN(@STRING);

IF(@len > 0)

BEGIN

WITH CTE AS (

SELECT CHARINDEX(CHARS,@STRING,1) x

, CAST(REVERSE(LEFT(REVERSE(LEFT(@STRING,

CHARINDEX(CHARS,@STRING,1)-1)), PATINDEX('%[^0-9,.]%',

REVERSE(LEFT(@STRING,CHARINDEX(CHARS,@STRING,1)-1)) + 'Z')-1))

AS VARCHAR(50)) AS RESULT

FROM @CHARACTERS

)

Insert Into msgDetailIn_Tb

(fk_visbox_Id, fk_msgIn_id,mvoltage,mnorth,meast,mtime,mtemperature,mheight,mcompraser,mluman)

Select

@visid,@msginID ,MAX([1]) Col1,MAX([2]) Col2,MAX([3]) Col3,MAX([4]) Col4,MAX([5]) Col5,MAX([6]) col6,MAX([7]) col7,MAX([8]) col8

From (SELECT 1 pid,ROW_NUMBER() OVER(Order by x) pivotcol,*

FROM CTE

WHERE LEN(RESULT)>0) s

pivot(max(RESULT)

FOR pivotcol in ([1],[2],[3],[4],[5],[6],[7],[8])) pvt

group by pid

END

THere is another issue, on the original you limit the query LEN(Result)>2 however the length of C is 1 so you'd never get it returned anyway, hence the change to WHERE LEN(RESULT)>0