CODE PASSED:

DECLARE @table TABLE (raw_payload VARCHAR(200))

INSERT INTO @Table SELECT raw_payload FROM raw_data

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

RESULTS:

(2551671 row(s) affected)

(624 row(s) affected)

Server: Msg 536, Level 16, State 3, Line 5

Invalid length parameter passed to the substring function.

SAMPLE RESULTS:

S9000NULLNULL

UV100.0NULL

UV101.0NULL

UV102.0NULL

UV103.0NULL

UV104.0NULL

Third column is not null in these cases but that is what is diplayed.