Varchar(MAX) and Split Function

[Elegabalus]

I'm using the split function mentioned in the FAQ (

http://www.tek-tips.com/faqs.cfm?fid=5207),

and it's been working great for me. I pass in a delimited string, and it converts it to a table format.

However, recently I needed to use it to pass in a very long string, and it has stopped working correctly.

If I use that function with the following code, it will return a table with 4000 rows (instead of the 1,000,000 rows expected).

DECLARE @test varchar(max)
SELECT @test = replicate('t|', 1000000)

SELECT Value
FROM dbo.Split(@test, '|')

I have gone into the function code and changed the @InputText field to Varchar(MAX), as well as changing the Value field to Varchar(MAX), but when I do that, it simply returns a table with 4001 rows.

Anyone with an idea how to fix this? Or what the problem is?

 

[gmmastros]

In SQL Server, a varchar variable is limited to 8000 characters. So, anything more than that will get truncated.

If you really need to pass that many items in to a stored procedure, you'll need to use a text data type and send the data in as XML. You can then parse the XML from with in a stored procedure to get the data back in to table form.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Elegabalus]

Hmm...I'm using SQL Server 2005 (sorry, should have specified that). I thought 2005, with varchar(max) meant that the size limit of the variable was much, much larger than 8000 characters.

Is this not true?

 

[gmmastros]

Ah... well. I gave you a SQL-2000 answer to a SQL-2005 question. Sorry.

I'm not too familiar with sql2k5, sorry.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

So, here's your sql2k5 answer...

According to BOL...

If character_expression is not of type varchar(max) or nvarchar(max), REPLICATE truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, character_expression must be explicitly cast to the appropriate large-value data type.

So....

DECLARE @test varchar(max)
SELECT @test = replicate([!]convert(VarChar(Max),[/!] 't|'[!])[/!], 1000000)

SELECT Value
FROM dbo.Split(@test, '|')

On my computer, this took 2 minutes and 17 seconds to run.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom