Overblog Follow this blog
Edit post Administration Create my blog
Balavardhan Reddy Narani

Split Text In Stored Procedure

March 13 2009 , Written by Balavardhan Reddy Published on #SQL Server2005

Here by I am providing a sample, It accepts two parameters, UserName, which is a string and an IdString which is a '~' Concatenated String of JOB IDs the user have been selectd through the Check boxes in the Job listing Page.

//Format of the IdString is ~71~5~88~1043~9

CREATE PROCEDURE dbo.SplitAndInsert
(
@userName varchar(100),
@idString varchar(100)
)
AS
SET NOCOUNT ON

DECLARE @splitstring varchar(100)
DECLARE @substring varchar(50)

Set @splitstring = @idString

WHILE (CHARINDEX('~',@splitstring ,1)<>0)
BEGIN

SET @substring = substring(@splitstring ,1,CHARINDEX('~',@splitstring ,1)-1)
-- Find Substring up to Separator
PRINT @substring


if @substring <> 0
INSERT INTO CH_APPLIED_INFO
(USER_NAME, JOB_ID)
VALUES (@userName,@substring)

SET @splitstring = substring(@splitstring ,Len(@substring)+2,Len(@splitstring ))
-- SET The Original String after the Split

END
SET NOCOUNT OFF

RETURN



User Selects the Select All check box and clicks Submit. All the IDs of selected check boxes are concatenated and send to the Stored Procedures.
Stored Procedure accepts Two Patameters UserName and IdString

Id String is in the format ~71~5~88~1043~9. The special character '~' is used to concatenate the Ids.

Stored procedure splits the IdString and get each seperate id and update the database with the UserName

The Table(CH_APPLIED_INFO) will be updated as

UserName JobID
Balu     
71
Balu       5
Balu      88
Balu    1043
Balu       9



Share this post

Repost 0

Comment on this post

motor trade insurance comparison 02/08/2012 11:43


Great work. I am looking for material like this and it seems that you have lot of experience in creating stored procedures. Thanks for sharing it.

Muay Thai 01/14/2012 18:49


Looks good, thanks.

Dissertation help 02/21/2011 11:49



This is a very very informative post. This will certainly help those guys are are in need of this kind of coding.



nyc therapists 12/23/2010 10:46



Thanks for sharing this, I think that this will help to provide a great information, keep on posting more blogs like this.



Preethy 10/21/2010 11:42



Its good Mr.Balu... u had wrote the conspt of split by using checkboxes right? but i need just t split all the names in the name field of one table and to split that string by comma...
can u provide me? its urgent........