How to assign large concatenated string and split it in sql server 2008

I have a concatenated string that i have taken as nvarchar(max) but it is retrieving only 9998 characters only.

I want to get all the characters that are in the concatenated string.

How can I retrieve all the characters?

ALTER function [utils].[udf_SplitString] 

(
@iSearchText NVARCHAR(MAX)
,@iSearchExpr VARCHAR(10) 
)
Returns @Results Table(id integer identity,SearchText NVARCHAR(MAX),ElementV开发者_Go百科alue VARCHAR(max))
As
BEGIN

Insert into @Results
(SearchText,ElementValue)
------- Split........
SELECT SearchText,
NullIf(SubString(SearchExpr + SearchText + SearchExpr , PositionedAt , CharIndex(SearchExpr , SearchExpr + SearchText + SearchExpr , PositionedAt) - PositionedAt) , '') AS SearchText
FROM (select numberid PositionedAt  from utils.numbers) Occurences, 
(
select 
@iSearchText as SearchText,
@iSearchExpr as SearchExpr
) dual
WHERE PositionedAt <= Len(SearchExpr + SearchText + SearchExpr) AND SubString(SearchExpr + SearchText + SearchExpr , PositionedAt - 1, 1) = SearchExpr 
AND CharIndex(SearchExpr , SearchExpr + SearchText + SearchExpr , PositionedAt) - PositionedAt > 0
------------ End of Split
Return

End

Your split function is based the "numbers" table technique, using your utils.numbers?

What does this say?

SELECT COUNT(*), MAX(numberid) utils.numbers

Do you have enough numbers to deal with the full string length, potentially 1 billion because it is nvarchar(max)?

I suspect you have only 10000 rows which is causing your truncation. The 9998 comes from the last , position before you run out of numbers

CREATE Function [dbo].[ParseStringList]  (@StringArray nvarchar(max) )  
Returns @tbl_string Table  (ParsedString nvarchar(max))  As  

BEGIN 

DECLARE @end Int,
        @start Int

SET @stringArray =  @StringArray + ',' 
SET @start=1
SET @end=1

WHILE @end<Len(@StringArray)
    BEGIN
        SET @end = CharIndex(',', @StringArray, @end)
        INSERT INTO @tbl_string 
            SELECT
                Substring(@StringArray, @start, @end-@start)

        SET @start=@end+1
        SET @end = @end+1
    END

RETURN
END

Use it like this:

Select ParsedString From dbo.ParseStringList(@StringArray)