SQL Server - Parsing an input string -- help please

Hello,

I want to pass in to my procedure an input string with values delimited by a semicolon

'100;200;300;400;'

and I want to parse this string and use the individual values in an insert statement loop within a  transaction.

How can I do this?

Thanks,

Burak

Check out the Sample "Parse a comma delimited comma.."

http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=1110&lngWId=5

http://www.codeproject.com/database/SQL_UDF_to_Parse_a_String.asp

http://www.planetsourcecode.com/vb/scripts/ShowCode.asp?txtCodeId=1110&lngWId=5

hello,

i'm going to use the split function in

http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=653&lngWId=5

how do i  store the returned strings in my stored procedure after i call the split function?

thanks,

burak

hello,

i'm going to use the split function in

http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=653&lngWId=5

how do i  store the returned strings in my stored procedure after i call the split function? how do i use these values ina transaction loop.

thanks,

burak

will take the entire delimited string and return a table that you can program against:

CREATE  FUNCTION [dbo].[fn_Split](@text varchar(8000), @delimiter varchar(20) = ' ')
RETURNS @Strings TABLE
(   
  position int IDENTITY PRIMARY KEY,
  value varchar(8000)  
)
AS
BEGIN
DECLARE @index int
SET @index = -1
WHILE (LEN(@text) > 0)
  BEGIN 
    SET @index = CHARINDEX(@delimiter , @text) 
    IF (@index = 0) AND (LEN(@text) > 0) 
      BEGIN  
        INSERT INTO @Strings VALUES (@text)
          BREAK 
      END 
    IF (@index > 1) 
      BEGIN  
        INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))  
        SET @text = RIGHT(@text, (LEN(@text) - @index)) 
      END 
    ELSE
      SET @text = RIGHT(@text, (LEN(@text) - @index))
    END
  RETURN
END

-- Example usage in a stored proc, to insert into Table variable:
/*
DECLARE @WORDS TABLE
(
ID INT IDENTITY (1,1),
string varchar(100)
)
 
INSERT INTO @WORDS
SELECT   VALUE AS WORD  FROM dbo.fn_Split( @WordsToInsert,' ')
*/

Peter,

I wrote a stored procedure using your function and it works fine. I was wondering how I could make the while statement at the end more elegant and add transaction functionality into it?

CREATE PROCEDURE dbo.wsp_Roles_AssignSchoolsByUserId
@UserId uniqueidentifier,
@Schools varchar(8000)
AS

-- declare a table to hold school names
DECLARE @USER_SCHOOLS TABLE
(
ID INT IDENTITY (1,1),
SCHOOL char(4)
)
 
-- parse the delimited list of schools into individual schools
-- and insert the school names into the user_schools table
INSERT INTO @USER_SCHOOLS
SELECT   VALUE AS SCHOOL  FROM dbo.fn_Split( @Schools,';')

-- check to see if table has any records
DECLARE @COUNT int
SELECT @COUNT = count(*)  FROM @USER_SCHOOLS
IF @COUNT > 0
BEGIN
   -- select individual school names and insert them along with the user id
   -- into  tbl_UsersInSchools
   DECLARE @SCHOOL char(4)
  
    WHILE( @COUNT > 0 )
    BEGIN
                
                 SET @SCHOOL = (SELECT TOP 1 SCHOOL from  @USER_SCHOOLS)
                 INSERT INTO tbl_UsersInSchools VALUES (@UserId, @SCHOOL)
    DELETE FROM @USER_SCHOOLS WHERE SCHOOL = @SCHOOL 
                SET @COUNT = @COUNT - 1
    END
END
GO

Here is some SQL code which might be useful for you, you can customize it as per your requirements. This does not use a table variable so should have a faster run time.

declare @school  varchar(8000)
declare @pos1 int
declare @pos2 int
declare @err_code INT

set @pos1 = 1
set @pos2 = 1

set @school = '1232;23;45;56;746;'

set @pos2 = charindex(';',@school)

while (@pos1<@pos2)
begin
   begin transaction
        print substring(@school,@pos1,@pos2-@pos1) 
       -- Insert statement
          if (@@Error>0)
          begin
                 @err_code = @@Error
          end
        -- Update Statement
          if (@@Error>0)
          begin
                 @err_code = @@Error
          end
        -- Delete statement
          if (@@Error>0)
          begin
                 @err_code = @@Error
          end
  
    commit transaction

    if(@err_code>0)
    begin
            PRINT 'Unexpected error occurred!' + STR(@err_code)
             Rollback transaction   
   end     
   set @pos1 = @pos2 + 1
   set @pos2 = @pos2+charindex(';',substring(@school,@pos1,len(@school)- @pos1+1))
end

Hope this helps.