0

I have a process that needs to split a string of key-value pairs. To do so, I'm using a combination of a temp table that has computed columns to calculate the key and the value from each key-value pair, and a string_split function in the INSERT statement to separate the key-value pairs into rows. This is how the table looks like:

CREATE TABLE #KeyValuePairs (
  [PK]       uniqueidentifier NOT NULL,
  [KeyValue] varchar(500) NOT NULL,
  [Key] AS CONVERT(varchar(35), SUBSTRING([KeyValue],1,CHARINDEX('=',[KeyValue])-1)) PERSISTED NOT NULL,
  [Value] AS SUBSTRING([KeyValue],CHARINDEX('=',[KeyValue]) + 1, LEN([KeyValue]) - CHARINDEX('=',[KeyValue])) PERSISTED NULL
); 

The clustered index just helps me to find a specific combination of primary key and value key efficiently. The following is how I insert data into it:

INSERT #KeyValuePairs ([PK], [KeyValue])
SELECT T.[PK], kv.[value]
FROM BaseTable T
CROSS APPLY string_split([KeyValueStringColumn],'*') kv
WHERE [KeyValueStringColumn] != ''
  AND kv.[value] != ''

Occasionally, this query fails with the following error:

Invalid length parameter passed to the LEFT or SUBSTRING function.

If I remove the computation from the table definition, leaving the fields NULL by the INSERT and instead run an UPDATE afterwards with the exact same formula, the UPDATE succeeds always.

  • What should be values for columns [Key] and [Value] if [KeyValue] doesn't have = inside? – Denis Rubashkin Nov 22 '19 at 12:58
  • Good question, but I don't have that case ever in the data I'm processing. The column [KeyValueStringColumn] is either an empty string, or it contains key-value pairs, where the pairs are separated by * and the key and value by =. That and because an UPDATE after the insert never fails leads me to the only explanation being that the computation is done for blank fields even though they do not get inserted to the table. – Sebastian H. Nov 22 '19 at 14:55
1

This is a similar issue to the one discussed here, but I think it deserves its own question/solution as in my case the issue was not the use of substring in a query, but in a table that's being inserted to. Though, the reason apparently is the same ... as Aaron Bertrand stated in his answer to another question:

... because you can't always rely on SQL Server filtering rows before attempting calculations.

While that's understandable in a case where the filter and the computation happen in the same query, it is not so obvious that the same appears to happen when inserting data into a table that has computed columns. Apparently, the persisted columns are computed before being inserted into the table, therefore (I guess depending on which plan the optimizer chose) also computing values of rows that due to the filter condition are not being inserted into the table. Changing the table definition making sure the length parameter cannot become negative solved the problem.

CREATE TABLE #KeyValuePairs (
  [PK]       uniqueidentifier NOT NULL,
  [KeyValue] varchar(500) NOT NULL,
  [Key] AS CONVERT(varchar(35), SUBSTRING([KeyValue],1,NULLIF(CHARINDEX('=',[KeyValue]),0)-1)) PERSISTED NOT NULL,
  [Value] AS SUBSTRING([KeyValue],CHARINDEX('=',[KeyValue]) + 1, LEN([KeyValue]) - NULLIF(CHARINDEX('=',[KeyValue]),0)) PERSISTED NULL
|improve this answer|||||
  • 1
    You could use NULLIF function to avoid wrong values: NULLIF(CHARINDEX('=',[KeyValue]), 0). If the string doesn't contain =, SUBSTRING will return NULL instead of throwing the error. That will be much shorter than CASE. – Denis Rubashkin Nov 22 '19 at 15:17
  • Good point, I've edited the answer using NULLIF instead of CASE. – Sebastian H. Dec 10 '19 at 13:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.