Tally table and substring error

Question

Post reply

Tally table and substring error

Lowell

SSC Guru

Points: 323518
More actions
March 2, 2010 at 9:38 am

#145675

just trying to use a tally table to split a string into rows, based on CrLf;
i'm getting an error :
Msg 536, Level 16, State 5, Line 19
Invalid length parameter passed to the SUBSTRING function.
i *think* it's because there are multiple trailing CrLf at the end of the document, but i swear this never happened to me before. I've used a tally split function lots of times, just trying to do it inline here.
maybe it's because my string ENDS with the character i'm splitting on?
Can someone rub my nose in it and show me what I'm doing wrong?
declare @input varchar(max)
set @input = '
--for users who are too lazy to type "SELECT * FROM"
CREATE procedure sp_show
--USAGE: sp_show gmact
@TblName varchar(128)
--WITH ENCRYPTION
As
Begin
exec(''Select * from '' + @TblName)
End
'
;WITH E01(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), -- 10 or 10E01 rows
E02(N) AS (SELECT 1 FROM E01 a, E01 b), -- 100 or 10E02 rows
E04(N) AS (SELECT 1 FROM E02 a, E02 b), -- 10,000 or 10E04 rows
E08(N) AS (SELECT 1 FROM E04 a, E04 b), --100,000,000 or 10E08 rows
--E16(N) AS (SELECT 1 FROM E08 a, E08 b), --10E16 or more rows than you'll EVER need,
Tally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E08),
ArrayAsTable AS (SELECT
substring(@input,N + 1,CHARINDEX(CHAR(13),@input,N + 1)-(N + 1)) as element
FROM Tally
WHERE substring(@input,N,1) = CHAR(13)
and N < len(@input)
)
SELECT
Element
FROM ArrayAsTable
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Lowell,

Try this function, and pass in the char(13) + char(10) as the 2 character deliminater.

CREATE function [dbo].[DelimitedSplit2] (

@pString varchar(max),

@pDelimiter varchar(2)

)

returns table

as

return

with

a1 as (select 1 as N union all

select 1 union all

select 1),

a2 as (select

1 as N

from

a1 as a

cross join a1 as b),

a3 as (select

1 as N

from

a2 as a

cross join a2 as b),

a4 as (select

1 as N

from

a3 as a

cross join a2 as b),

Tally as (select top (DATALENGTH(@pString))

row_number() over (order by N) as N

from

a4),

ItemSplit(

ItemOrder,

Item

) as (

SELECT

N,

SUBSTRING(@pDelimiter + @pString + @pDelimiter,N + DATALENGTH(@pDelimiter),CHARINDEX(@pDelimiter,@pDelimiter + @pString + @pDelimiter,N + DATALENGTH(@pDelimiter)) - N - DATALENGTH(@pDelimiter))

FROM

Tally

WHERE

N < DATALENGTH(@pDelimiter + @pString + @pDelimiter)

AND SUBSTRING(@pDelimiter + @pString + @pDelimiter,N,DATALENGTH(@pDelimiter)) = @pDelimiter --Notice how we find the delimiter

)

select

row_number() over (order by ItemOrder) as ItemID,

Item

from

ItemSplit

Lowell SSC Guru Points: 323518 More actions · Answer 2

yeah Lynn, i had done basically the same thing...when i use a function, the function works, and i get the delimited results as expected, but when i try to do it inline, so my code has no dependancies, i get the error.

i think i'm doing something wrong. the only real difference between yours and mine was the two character delimiter.

:using your function:

declare @input varchar(max),

@vbCrLf char(2)

SET @vbCrLf=CHAR(13) + CHAR(10)

set @input = '

--for users who are too lazy to type "SELECT * FROM"

CREATE procedure sp_show

--USAGE: sp_show gmact

@TblName varchar(128)

--WITH ENCRYPTION

As

Begin

exec(''Select * from '' + @TblName)

End

GO'

select * from dbo.DelimitedSplit2(@input,@vbCrLf)

mine with your code inline:

declare @input varchar(max),

@vbCrLf char(2)

SET @vbCrLf=CHAR(13) + CHAR(10)

set @input = '

--for users who are too lazy to type "SELECT * FROM"

CREATE procedure sp_show

--USAGE: sp_show gmact

@TblName varchar(128)

--WITH ENCRYPTION

As

Begin

exec(''Select * from '' + @TblName)

End

'

;WITH E01(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1), -- 10 or 10E01 rows

E02(N) AS (SELECT 1 FROM E01 a, E01 b), -- 100 or 10E02 rows

E04(N) AS (SELECT 1 FROM E02 a, E02 b), -- 10,000 or 10E04 rows

E08(N) AS (SELECT 1 FROM E04 a, E04 b), --100,000,000 or 10E08 rows

--E16(N) AS (SELECT 1 FROM E08 a, E08 b), --10E16 or more rows than you'll EVER need,

Tally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E08),

ItemSplit(

ItemOrder,

Item

) as (

SELECT N,

SUBSTRING(@vbCrLf + @input + @vbCrLf,N + DATALENGTH(@vbCrLf),CHARINDEX(@vbCrLf,@vbCrLf + @input + @vbCrLf,N + DATALENGTH(@vbCrLf)) - N - DATALENGTH(@vbCrLf))

FROM

Tally

WHERE N < DATALENGTH(@vbCrLf + @input + @vbCrLf)

AND SUBSTRING(@vbCrLf + @input + @vbCrLf,N,DATALENGTH(@vbCrLf)) = @vbCrLf --Notice how we find the delimiter

)

select

row_number() over (order by ItemOrder) as ItemID,

Item

from

ItemSplit

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

I'm not sure at this time. This is something i will have to try a play with when I have a little more time. Nice SQL Puzzle, I enjoy those.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Lowell,

Try this change. I stumbled on to it so I can't explain (at this time) why it makes the in-line code work.

WHERE N < DATALENGTH(@vbCrLf + @input) -- REMOVED added @vbCrLf

Lowell SSC Guru Points: 323518 More actions · Answer 5

yep that did it; I'm looking at it now, not sure why there's a difference.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

WayneS SSC Guru Points: 95460 More actions · Answer 6

Lowell,

If you substitute this for the end of your code, you will see that @input does not end with a CRLF... just a CR. Maybe this is it?

ArrayAsTable AS (SELECT

substring(@input,N + 1,CHARINDEX(CHAR(13),@input,N + 1)-(N + 1)) as element,

N

FROM Tally

WHERE substring(@input,N,1) = CHAR(13)

and N < len(@input)

)

SELECT

N, substring(@input, N, 1), --Element

ASCII(substring(@input, N, 1))

FROM Tally

WHERE N < LEN(@input)

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95460 More actions · Answer 7

Okay, try N <= len(@input). It doesn't fix your issue, but it does now work on the whole string.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95460 More actions · Answer 8

Lowell,

This works for me...

declare @input varchar(max)

--set @input = char(13) + char(10) + '

set @input = '

--for users who are too lazy to type "SELECT * FROM"

CREATE procedure sp_show

--USAGE: sp_show gmact

@TblName varchar(128)

--WITH ENCRYPTION

As

Begin

exec(''Select * from '' + @TblName)

End

'

;WITH E01(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1), -- 10 or 10E01 rows

E02(N) AS (SELECT 1 FROM E01 a, E01 b), -- 100 or 10E02 rows

E04(N) AS (SELECT 1 FROM E02 a, E02 b), -- 10,000 or 10E04 rows

E08(N) AS (SELECT 1 FROM E04 a, E04 b), --100,000,000 or 10E08 rows

--E16(N) AS (SELECT 1 FROM E08 a, E08 b), --10E16 or more rows than you'll EVER need,

Tally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E08),

ArrayAsTable AS (SELECT

element = substring(@input,N + 1,CHARINDEX(CHAR(13),@input,N + 1)) ---(N + 1)) --as element,

--N

FROM Tally

WHERE substring(@input,N,1) = CHAR(13)

and N <= len(@input)

)

SELECT

Element

FROM ArrayAsTable

In ArrayAsTable, removed the trailing "-(N+1)"

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Lowell SSC Guru Points: 323518 More actions · Answer 9

Thanks Wayne;

I think the minor tweak Lynn suggested is better; the change you made gives some inaccurate/unexpected results, even though the code runs without error:

i think it's because i was splitting on char(13) and not a two char vbCrLf, but at least my code is working as expected now.

--for users who are too lazy to type "SELECT * FROM"

CREATE procedure sp_show --USAGE: sp_show gmact @TblName varchar(128)

--USAGE: sp_show gmact @TblName varchar(128) --WITH ENCRYPTION As Begin exec('Selec

@TblName varchar(128) --WITH ENCRYPTION As Begin exec('Select * from ' + @TblName) End

--WITH ENCRYPTION As Begin exec('Select * from ' + @TblName) End

As Begin exec('Select * from ' + @TblName) End

Begin exec('Select * from ' + @TblName) End

exec('Select * from ' + @TblName) End

End

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!