March 17, 2011 at 2:20 pm
I have a simple string splitting iTVF (attached) that im trying to use in an exist clause
SELECT * FROM dbo.fncSplitTable2('2;>;3','|') WHERE ITEM_ARRAY LIKE '[A,S][V,U][G,M]%'
this works as expected, no results
IF EXISTS(SELECT * FROM dbo.fncSplitTable2('2;>;3','|') WHERE ITEM_ARRAY LIKE '[A,S][V,U][G,M]%')
PRINT 'OK'
throws Invalid length parameter passed to the LEFT or SUBSTRING function. and i have no idea why!
Accepting any help/idea/direction!
It is also possible for me to change this function interely but I'd prefer it to remain as a iTVF
Thanks
Edit: first select was wrong. corrected
--
Thiago Dantas
@DantHimself
March 17, 2011 at 2:42 pm
If the string doesn't have the delimiter in it, you'll get a LEFT(string, 0) or LEFT(string, -1) essentially, and that is an invalid value for the LEFT function, hence the error.
You can mitigate that by appending the delimiter to the end of the string as part of the function, before it starts parsing.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 17, 2011 at 2:46 pm
GSquared (3/17/2011)
If the string doesn't have the delimiter in it, you'll get a LEFT(string, 0) or LEFT(string, -1) essentially, and that is an invalid value for the LEFT function, hence the error.You can mitigate that by appending the delimiter to the end of the string as part of the function, before it starts parsing.
the function does this. it appends the delimiter to the string before and after the string. thats why the simple select works. the question is why it doesn't work with EXISTS
Edit: the first select was wrong, fixed it
--
Thiago Dantas
@DantHimself
March 17, 2011 at 5:19 pm
dant12 (3/17/2011)
the function does this. it appends the delimiter to the string before and after the string. thats why the simple select works. the question is why it doesn't work with EXISTS
The reason is that SQL Server is free to reorder expressions when searching for a good execution plan. The in-line TVF is expanded into the calling query just like a view, and SQL Server optimizes the whole resulting text.
Your function assumes that certain expressions and predicates will be evaluated before others to avoid an error message. For some query plan shapes, this assumption does not hold: the result of the SUBSTRING is evaluated with the LIKE from the outer query before the safety conditions.
This is not a bug in SQL Server, but it is a bug in the function. I'm not a huge fan of in-line string splitters with an on-the-fly numbers table for a number of reasons, but if you really must use one try this:
ALTER FUNCTION
dbo.DelimitedSplit8K
(
@pString VARCHAR(7999),
@pDelimiter CHAR(1)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
WITH V(n)
AS (
SELECT 1
FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) V1 (n),
(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) V2 (n),
(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) V3 (n),
(VALUES (1),(1),(1),(1),(1),(1),(1),(1)) V4 (n)
),
Tally (n)
AS (
SELECT TOP (DATALENGTH(@pString)) ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM V
)
SELECT item_number = ROW_NUMBER() OVER (ORDER BY T.n),
item = SUBSTRING(@pString, T.n, CHARINDEX(@pDelimiter, @pString + @pDelimiter, T.n) - T.n)
FROM Tally AS T
WHERE SUBSTRING(@pDelimiter + @pString, T.n, 1) = @pDelimiter
;
That's based on some code provided by Jeff Moden. My preference for a string-splitting routine is to use SQLCLR.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 18, 2011 at 10:12 am
SQLkiwi (3/17/2011)
dant12 (3/17/2011)
the function does this. it appends the delimiter to the string before and after the string. thats why the simple select works. the question is why it doesn't work with EXISTSThe reason is that SQL Server is free to reorder expressions when searching for a good execution plan. The in-line TVF is expanded into the calling query just like a view, and SQL Server optimizes the whole resulting text.
Your function assumes that certain expressions and predicates will be evaluated before others to avoid an error message. For some query plan shapes, this assumption does not hold: the result of the SUBSTRING is evaluated with the LIKE from the outer query before the safety conditions.
thanks paul, that really answers the WHY. my function was also based on Jeff Moden's awsome string splitter, the choice for an on-the-fly tally was that changing database schema for a single function was a no-go, or so I was told.
Given that, my only choice is to drop the iTVF and use a while-based multi-statement one. Perf tested and it seems while-based speed > on-the-fly tally-based speed for multi-statement TVF
Thanks.
--
Thiago Dantas
@DantHimself
March 18, 2011 at 1:23 pm
dant12 (3/18/2011)
Given that, my only choice is to drop the iTVF and use a while-based multi-statement one. Perf tested and it seems while-based speed > on-the-fly tally-based speed for multi-statement TVF.
Yes, the in-line on-the-fly function has problems with some query plans (the numbers table ends up being generated once per row!) so for small string-splitting requirements, you're often best off using a multi-statement TVF.
Because mutli-statement TVFs only work well for small splitting needs, it doesn't really matter which technique you use. Don't try to use the msTVF for larger tasks though - it will absolutely suck.
This is why I prefer a SQLCLR function - it is always fast.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 18, 2011 at 1:30 pm
SQLkiwi (3/18/2011)
dant12 (3/18/2011)
Given that, my only choice is to drop the iTVF and use a while-based multi-statement one. Perf tested and it seems while-based speed > on-the-fly tally-based speed for multi-statement TVF.Yes, the in-line on-the-fly function has problems with some query plans (the numbers table ends up being generated once per row!) so for small string-splitting requirements, you're often best off using a multi-statement TVF.
Because mutli-statement TVFs only work well for small splitting needs, it doesn't really matter which technique you use. Don't try to use the msTVF for larger tasks though - it will absolutely suck.
This is why I prefer a SQLCLR function - it is always fast.
Paul
If we were already using CLR for something, i couldn't agree more with you! On my previous job i did perf testing with Adam Machanic's CLR string splitter and it flew like a beast, but right now there would be alot of documenting, testing, QA and everything just for this. This will be small though
Thanks again
--
Thiago Dantas
@DantHimself
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply