Try this... it's Adrian's original code with some conditions added...
SELECT LEFT(raw_payload, CHARINDEX('+', raw_payload, 1)-1),
CASE
WHEN LEN(raw_payload)-LEN(REPLACE(raw_payload,'+','')) >= 2
THEN
CAST(SUBSTRING(raw_payload,
CHARINDEX('+', raw_payload, 1) + 1,
CHARINDEX('+', raw_payload, CHARINDEX('+', raw_payload, 1) +1) - (CHARINDEX('+', raw_payload, 1) + 1))
AS FLOAT)
ELSE NULL END,
CASE
WHEN LEN(raw_payload)-LEN(REPLACE(raw_payload,'+','')) >= 3
THEN
CAST(SUBSTRING(raw_payload,
CHARINDEX('+', raw_payload, CHARINDEX('+', raw_payload, 1) +1) + 1,
LEN(raw_payload) - CHARINDEX('+', raw_payload, CHARINDEX('+', raw_payload, 1) +1) + 1)
AS FLOAT)
ELSE NULL END
FROM #SplitMe
--Jeff Moden
Change is inevitable... Change for the better is not.