I have a table with a column with a set of key-value pairs, separated by a comma delimiter. The grouped values may or may not include a delimiter at the end of the string with nothing after it – I have no control over whether there is a trailing delimiter or not.

I created a view to make it easier to select the value for a given key – it uses STRING_SPLIT to separate the key-values, and it will attempt to filter out the empty token at the end, if there is one. The second part of the view breaks apart the key and value by parsing substrings around the = (I think this is simpler than a second STRING_SPLIT would be).

This view works perfectly fine if I select all records from it, but as soon as I add a WHERE clause it errors out:

Msg 537, Level 16, State 2, Line 1 Invalid length parameter passed to
the LEFT or SUBSTRING function.

I can replicate this behaviour with a query using a hard-coded value without needing to create a whole table and view.

WITH tags AS (SELECT value
    FROM STRING_SPLIT('1=A,2=B,3=C,', ',')
    WHERE value <> ''),
tagValues AS 
    (SELECT 
        CAST(SUBSTRING(Value, 1, CHARINDEX('=', Value)-1) as int) AS Tag,
        SUBSTRING(Value, CHARINDEX('=', Value)+1, 100) AS Value
    FROM tags)
SELECT *
    FROM tagValues
    WHERE Tag = 1           /* including this line throws the error */

If I run the above query without the last line (just the select * from tagValues) it returns what I want and filters out the empty token:

If I try to run SELECT * FROM tagValues WHERE Tag = 1 then it seems to rework its query plan and throws the error, apparently evaluating the SUBSTRING functions in the tagValues portion of the CTE before applying WHERE value <> ''

I have also tried a filter WHERE CHARINDEX('=', Value) > 1 rather than WHERE value <> '' but get the same result.

Since this a view I can’t use temp tables to ensure I’m only processing key-value pairs – is there a way to write this query to force the filtering of empty tokens before trying to split the key-value pairs?