Where clause criterion on view causing error

Question

I have a view which breaks apart delimited list. For example if you have the following table with the following two columns:

ABC, A;B;C

Assuming the name "TestView", you would get the following:

Label  Source
--------------
ABC    A
ABC    B
ABC    C

The code is derived from here: Split one column into multiple rows

When I try to put a condition in the where clause of the view, I'm getting an error. "Invalid length parameter passed to the LEFT or SUBSTRING function." So anything like "select * from TestView where Source = 'A'" will cause that error.

Here's the code from the view:

CREATE VIEW [dbo].[TestView]
AS
WITH L0 AS (SELECT 1 AS c 
            UNION ALL 
            SELECT 1),
     L1 AS (SELECT 1 AS c 
               FROM L0 AS A, 
                    L0 AS B),
     L2 AS (SELECT 1 AS c 
              FROM L1 AS A, 
                   L1 AS B),
     L3 AS (SELECT 1 AS c 
              FROM L2 AS A, 
                   L2 AS B),
     --counts up to 256
     Numbers AS (SELECT ROW_NUMBER() OVER (ORDER BY c) AS n 
                   FROM L3)
SELECT Label,        
       LTRIM(RTRIM(SUBSTRING(valueTable.Sources, nums.n, charindex(N';', valueTable.Sources + N';', nums.n) - nums.n))) AS Source
  FROM Numbers AS nums 
  JOIN dbo.SourceTable AS valueTable ON nums.n <= CONVERT(int, LEN(valueTable.Sources)) 
                                    AND SUBSTRING(N';' + valueTable.Sources, n, 1) = N';'

score 1 · Answer 1 · answered May 20 '11 at 18:44

1

Invalid Length parameters usually mean you are passing a negative value to the substring function.

Find out what the result of this piece is for every record: charindex(N';', valueTable.Sources + N';', nums.n) - nums.n

I'll bet the charindex is returning a 0 indicating it couldn't locate that value and nums.n is greater than 0.

answered May 20 '11 at 18:44

NotMe

87,343
27
171
245

There is one entry on the main table that doesn't contain a value. Thanks for putting me on the correct trail. I'll report back. – geoffrobinson May 20 '11 at 18:51
There is something going on with the length of the text. But it still doesn't make sense to me why it works to produce the view but it fails in the where clause. – geoffrobinson May 20 '11 at 19:41

score 1 · Accepted Answer · answered May 21 '11 at 00:51

That link you provide links to another article (http://www.sqlservercentral.com/articles/Tally+Table/72993/) which provides a pretty performant splitter function.

You could try using this instead

CREATE FUNCTION dbo.DelimitedSplit8K
--===== Define I/O parameters
    (@pString VARCHAR(8000), @pDelimiter CHAR(1))
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000...
 -- enough to cover VARCHAR(8000)
  WITH E1(N) AS (
             SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
             SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
             SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
            ),                          --10E+1 or 10 rows
   E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
   E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS (--==== This provides the "zero base" and limits the number of rows right up front
                 -- for both a performance gain and prevention of accidental "overruns"
             SELECT 0 UNION ALL
             SELECT TOP (DATALENGTH(ISNULL(@pString,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
            ),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
             SELECT t.N+1
               FROM cteTally t
              WHERE (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0) 
            )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY s.N1),
    Item       =    SUBSTRING(@pString,s.N1,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000))
   FROM cteStart s
;

Then it would be a case of

with rawData (Label,rawString) as (select 'ABC','A;B;C')

select Label,Item From rawData
Cross Apply DelimitedSplit8K(rawData.rawString, ';')
WHERE Item = 'A'

The function is returning a table that works when I hardcode the parameters. However, when I run the test case you post I get the error "Incorrect syntax near '.'." — geoffrobinson, May 23 '11 at 13:31
Ok, figured it out. The Compatibility Level needed to be raised from 80 to 90. — geoffrobinson, May 23 '11 at 14:13

Where clause criterion on view causing error

2 Answers2