Split() function in SQL 2000
Few days ago, I finish a small .Net project, where I have to manage a survey for schools with dozens of dozens of web controls like textboxes, checkboxes, radiobuttons, etc...
The performance of the application was quite good, but saving data in SQL appear to be a disaster.
Very slow, so obviously a lot of Timeout errors. I tried everything, like increasing the running time for my application, or doing some code optimisation. Not really successful.
So I came to the idea of doing the most of the job on the SQL side. What I did is to send an all 'blob' of information to a stored procedure, including questions id and answers, and asked to SQL to split the data in a suitable way and store it.
Well the only problem is that in SQL you don't have any Split() function like in VB.
So I wrote my own function and it works so well, that I would like to share the function.
Notice: you have to create this as a User Defined function and not as a normal stored procedure.
CREATE FUNCTION Piece ( @CharacterExpression VARCHAR(8000), @Delimiter CHAR(1), @Position INTEGER)
RETURNS VARCHAR(8000)
AS
BEGIN
If @Position<1 return null
if len(@Delimiter)<>1 return null
declare @Start integer
set @Start=1
while @Position>1
BEGIN
Set @Start=ISNULL(CHARINDEX(@Delimiter, @CharacterExpression, @Start),0)
IF @Start=0 return null
set @position= @position-1
set @Start=@Start+1
END
Declare @End INTEGER
Set @End= ISNULL(CHARINDEX(@Delimiter, @CharacterExpression, @Start),0)
If @End=0 Set @End=LEN(@CharacterExpression)+1
RETURN SUBSTRING(@CharacterExpression, @Start, @End-@Start)
END
In your stored procedure you have just to call Piece with your expression to split.
Here we go with an example where I used # as a separator between fileds and values:
/****** Object: Stored Procedure dbo.Add_Answers Script Date: 10/03/2002 ******/
CREATE PROCEDURE Add_Answers
@RollNumber varchar(50),
@Table_survey varchar(80),
@String varchar(8000),
@DelFlag integer
AS
/** Read the Id_questionnaire from the Entry table using the parameter @RollNumber **/
declare @id_q as int
set @id_q = (select id_questionnaire from entry where Entry.School_rollnumber=@RollNumber)
/** Delete entries in the Table selected using id_questionnaire**/
declare @SelString nchar(500)
if @Delflag=1
BEGIN
set @SelString = '
Delete ' + @Table_survey + ' where Id_entry=' + convert(nchar(50),@Id_q)
execute sp_executesql @SelString
END
Declare @Position Integer
Declare @End varchar(50)
Declare @Substr nchar(50)
Declare @Subvalue nchar(50)
Set @Position=1
Set @Substr = (SELECT dbo.Piece(@String, '#', @Position))
Set @End= ISNULL(@Substr,'')
while @End <> ''
BEGIN
Set @Substr = (SELECT dbo.Piece(@String, '#', @Position))
Set @Subvalue = (SELECT dbo.Piece(@String, '#', @Position+1))
Set @End= ISNULL(@Substr,'')
if @End<>''
BEGIN
set @SelString = 'insert into ' + @table_survey + ' ( Id_entry, Id_q, Answer) values ( ' + convert(varchar(20),@id_q) + ', ''' + RTRIM(@Substr) + ''' ,''' + RTRIM(@Subvalue) + ''')'
execute sp_executesql @SelString
Set @Position =@Position+2
END
END
GO