Databases
StackOverflow2013
Postsdbo
POWeird error in udf when included in "WHERE...IN" clause
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWeird error in udf when included in "WHERE...IN" clause
    text
    copied!<p>I have a function that splits up a string (pasted at the end for clarity). This function works as expected when used alone. Example:</p> <pre><code>SELECT value FROM dbo.mg_fn_Split('2#1','#') </code></pre> <p>Returns</p> <pre><code>-- value -- -- 2 -- -- 1 -- ----------- </code></pre> <p>But when used in a "WHERE IN" clause, as in this example (more on tableA later on):</p> <pre><code>SELECT * FROM TableA WHERE TableA.id IN ( SELECT value FROM dbo.mg_fn_Split('2#1','#') ) </code></pre> <p>I get the error: "Invalid length parameter passed to the LEFT or SUBSTRING function."</p> <p>TableA is used here as an example. Using different tables (assuming they have the id column) sometimes returns correct results, while on other tables I get the error.</p> <p>I'm assuming it has something to do with order of execution, but I still fail to see what could "corrupt" the function.</p> <p>I'm looking for a "what's happening" explanation, not a "use this instead". I know I can use joins for example to get the results.</p> <p>The function definition:</p> <pre><code>-- Description: Returns a table containing the results of a string-split operation. -- Params: -- DelimitedList: The string to split -- Delimiter: The delimiter char, defaults to ',' -- Columns: -- Position - The char index of the item -- Value - The actual item -- ============================================= CREATE Function [dbo].[mg_fn_Split] ( @DelimitedList nvarchar(max) , @Delimiter nvarchar(2) = ',' ) RETURNS TABLE AS RETURN ( With CorrectedList As ( Select Case When Left(@DelimitedList, Len(@Delimiter)) &lt;&gt; @Delimiter Then @Delimiter Else '' End + @DelimitedList + Case When Right(@DelimitedList, Len(@Delimiter)) &lt;&gt; @Delimiter Then @Delimiter Else '' End As List , Len(@Delimiter) As DelimiterLen ) , Numbers As ( Select TOP( Coalesce(DataLength(@DelimitedList)/2,0) ) Row_Number() Over ( Order By c1.object_id ) As Value From sys.columns As c1 Cross Join sys.columns As c2 ) Select CharIndex(@Delimiter, CL.list, N.Value) + CL.DelimiterLen As Position , Substring ( CL.List , CharIndex(@Delimiter, CL.list, N.Value) + CL.DelimiterLen , CharIndex(@Delimiter, CL.list, N.Value + 1) - ( CharIndex(@Delimiter, CL.list, N.Value) + CL.DelimiterLen ) ) As Value From CorrectedList As CL Cross Join Numbers As N Where N.Value &lt;= DataLength(CL.List) / 2 And Substring(CL.List, N.Value, CL.DelimiterLen) = @Delimiter ) </code></pre> <p><strong>EDIT:</strong> I've set up a fiddle to exhibit this: <a href="http://sqlfiddle.com/#!3/9f9ff/3" rel="nofollow">http://sqlfiddle.com/#!3/9f9ff/3</a></p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload