0

I'm currently using function to split string separated by semicolons. The function is not able to split string 12 000 characters long. It runs for a few minutes and then SSMS freezes.

Can you please suggest me function, which is able to handle this? I will post the function I use as soon as I get to PC.

Thank you.

Function (now working as expected):

/* fce SplitList start */
Declare @CommaDelimitedFieldNames Varchar(MAX)
Declare @CharToFind VarChar(10)
Set @CommaDelimitedFieldNames = REPLACE(@String,' ','')
Set @CharToFind = ';'

Declare @Tbl_FieldNames Table
(
Position Integer Identity(1,1),
FieldName VarChar(8000)
)

Set @CommaDelimitedFieldNames = @CommaDelimitedFieldNames + @CharToFind
Declare @Pos1 Int
Declare @pos2 Int

Set @Pos1=1
Set @Pos2=1

While @Pos1<DataLength(@CommaDelimitedFieldNames)
Begin
Set @Pos1 = CharIndex(@CharToFind, @CommaDelimitedFieldNames,@Pos1)
Insert @Tbl_FieldNames Select Cast(Substring(@CommaDelimitedFieldNames,@Pos2,@Pos1-@Pos2) As NVARCHAR(MAX))
Set @Pos2=@Pos1+1
Set @Pos1 = @Pos1+1
End
/* fce Splitlist end */
Rutz
  • 89
  • 4
  • 12
  • Sql Server has a limit on the number of characters you can store in a varchar(string data types). You will have to check for your CSV string for length before trying in a TSQL. http://stackoverflow.com/questions/7611394/maximum-size-of-a-varcharmax-variable – DhruvJoshi Jan 29 '14 at 07:02
  • Thank you for your answer. I'm using NVARCHAR(MAX) to declare variable. It returns the whole string - no characters missing. I will post the function I use. The problem must be in the function itself. – Rutz Jan 29 '14 at 07:27
  • How are you splitting? It might be a function you use which only supports varchar(4000) or something. On an off remark - splitting such a string in the database seems like making it do work it shouldn't be doing :) – Allan S. Hansen Jan 29 '14 at 07:32
  • The limit is max 2 GB of storage, so a 12000 character string whether it's VARCHAR or NVARCHAR, doesn't even come close. SQL Server has issues displaying long strings, but a N/VARCHAR(MAX) datatype will store any number of characters until that 2 GB limit. @Ruts: We don't know what your function looks like, so can't comment on that. But you might find an answer here, for instance: http://stackoverflow.com/questions/2647/split-string-in-sql – Kahn Jan 29 '14 at 07:35
  • Thank you very much for your answers. I will check the links. I've just posted the function I'm currently using. – Rutz Jan 29 '14 at 07:40
  • @Allan S. Hansen Input string comes from the SSRS parameters - customer needs to filter specific documents (semicolon-separated values). – Rutz Jan 29 '14 at 07:41
  • The function is now working as expected. I had to set variable @CommaDelimitedFieldNames to Varchar(MAX). Varchar(8000) - too long, NVARCHAR(MAX) - not working. – Rutz Jan 29 '14 at 08:09
  • At what point do you start thinking to yourself "maybe I should use a datatype, such as XML or table-valued parameters, that has a *natural* way of representing multiple values, instead of trying to stuff things inside a string"? – Damien_The_Unbeliever Jan 29 '14 at 08:11
  • Try printing the position value or check if a comma separated value is greater than FieldName which can hold 8000 chars (VarChar(8000)) – DhruvJoshi Jan 29 '14 at 08:24
  • @Damien_The_Unbeliever Because the string is input from the user - text parameter in SSRS report. This is the reason why it is a string separated by semicolons. – Rutz Jan 29 '14 at 08:49

1 Answers1

0

Thank you for all your suggestions. The problem was in the function itself. I should have used varchar(max) instead of nvachar(max) for variable @CommaDelimitedFieldNames (now corrected in question). Splitting the 12 000 characters long string now takes about 3 seconds.

Rutz
  • 89
  • 4
  • 12