Solved

SQL Server 2000 - Spliting up a string

Posted on 2008-10-16
8
311 Views
Last Modified: 2012-05-05
Hi,

I have a stored procedure that splits a string '#100:101#' into string 100 and string 101 and sends them to another sp but i am using a splitting function which is not compatible with the version of sql i is to run on.
Is there another way to achieve this, maybe not as complicated?? My splitting function is below!
declare @str2 nvarchar(200)

	declare @a2 nvarchar(100)

	declare @b2 nvarchar(100)

	declare @bINT int

	create table #x_process_temp2 (y nvarchar(400))

	create table #y_process_temp2 (id int identity, y nvarchar(400))
 

	declare x_c2 cursor for select y from #x_process_temp2

	declare y_c2 cursor for select y from #y_process_temp2 order by id
 
 

--DECLARE @status INT

--set @status = 0
 

				insert into #x_process_temp2 

				select strval from split('#888:999#', '#')

				where strval != ''

			

				--select * from #x_process_temp

			

				open x_c2 

				fetch x_c2 into @str2

			

				while @@fetch_status = 0

				begin

					delete from #y_process_temp2

			

					select @str2 = @str2 + ':'

			

					insert into #y_process_temp2 (y)

					select strval from split(@str2, ':')

					where strval != ''

			

					-- select * from #y_process_temp

			

					open y_c2

					fetch y_c2 into @a2

					fetch y_c2 into @b2

					close y_c2

			

					select @a2 as value1

					select @b2 as value2

			

					fetch x_c2 into @str2

				end

			

				close x_c2

				deallocate x_c2

			

				deallocate y_c2

			

				drop table #x_process_temp2

				drop table #y_process_temp2
 

			--set @bINT = convert(integer,@a2)
 

			--SELECT @abuseid = max(AbuseID) FROM Abuse WHERE ClientID = @clientid

			--delete from MultipleRefItems where clientid = @clientid and groupid = @ReportedTimeframe and abuseid = @abuseid and abusecomplaintid = @bINT

			--SET @status = @status + @@ERROR

		

			--if (datalength(@a)  > 0)

				--exec  res_ExtractAndPopMultiItems @clientid, @ReportedTimeframe, @abuseid, @bINT, @b2,@status

Open in new window

0
Comment
Question by:caoimhincryan
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22729887
how do you want to get the output? in a table variable?
0
 
LVL 14

Expert Comment

by:Binuth
ID: 22729889
create attached function and try this sql query

select Rownum,REPLACE(ID,'#','') AS ID from fnSplitterforString('#888:999#',':')
CREATE FUNCTION [dbo].[fnSplitterforString] 

(

	 @IDs Varchar(8000) 

	,@chrDelimiter CHAR(1)

)  

RETURNS @Tbl_IDs Table  (RowNum INT,ID VARCHAR(1000))  AS  

BEGIN 

	 -- Append comma

	 SET @IDs =  @IDs + @chrDelimiter

	 -- Indexes to keep the position of searching

	 DECLARE @Pos1 INT

	 DECLARE @pos2 INT

	 DECLARE @RowNum INT

	 

	 -- Start from first character 

	 SET @Pos1=1

	 SET @Pos2=1

	 SET @RowNum = 1	

	 WHILE @Pos1<Len(@IDs)

	 BEGIN

		  --SET @Pos1 = CharIndex(',',@IDs,@Pos1)

		  SET @Pos1 = CharIndex(@chrDelimiter,@IDs,@Pos1)

		  INSERT @Tbl_IDs SELECT  @RowNum,Cast(Substring(@IDs,@Pos2,@Pos1-@Pos2) AS VARCHAR(1000))

		  -- Go to next non comma character

		  SET @Pos2=@Pos1+1

		  -- Search from the next charcater

		  SET @Pos1 = @Pos1+1

		  SET @RowNum = @RowNum + 1	

	 END 

	 RETURN

End

Open in new window

0
 

Author Comment

by:caoimhincryan
ID: 22730355
Thanks for the reply,

The function which splits up the string is just part of a larger atored procedure. I want the string split into two variable strings which i want to add as parameters to another to stored procedure call.

If i use the 'fnSplitterforString' function, how can i pass the results to the function call

exec  res_ExtractAndPopMultiItems @clientid, @Reported, @BId, @a, @b

where @a, @b are the parts of my split string.

Thanks :)
0
 
LVL 14

Expert Comment

by:Binuth
ID: 22730606
then ... you should call the function twice
like...
DECLARE @a AS INT

DECLARE @b AS INT

select @a = REPLACE(ID,'#','')   from fnSplitterforString('#888:999#',':') where Rownum = 1

select @b = REPLACE(ID,'#','')   from fnSplitterforString('#888:999#',':') where Rownum = 2

Open in new window

0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:caoimhincryan
ID: 22731136
That would work except (which i didnt mention in the beginning) is that the string can be like this '#524:458#526:457#' or bigger and i want to split it into smaller strings by # and into their two values by :

I had attempted some sort of replace in a stored procedure where (if it worked) it would read from # to # serarate the string by : send off the valuse the replace the string (# to #) with nothing and continue to next part of the string...if that was possible. My attempt is below:)

Any help is much appreciated.
 


--CREATE PROCEDURE res_SlipItems(@ClientID int, @groupID int,  @ID int, @Multi ntext,@abc int = 0, @status int  output)
 

CREATE PROCEDURE res_SlipItems(@string ntext,  @status int  output)

as
 

declare @TeamIn nvarchar(4000)

declare @TeamInSearch nvarchar(4000)
 

declare @TeamInb nvarchar(4000)

declare @TeamInSearchb nvarchar(4000)
 

set @TeamInSearch=''
 

declare @Indx int

declare @IndxTeam int

declare @IndxTeam2 int

declare @LastIndx int

declare @start int

declare @length int
 

set @TeamIn = ''

set @IndxTeam = 1

set @IndxTeam2 = 1

set @Indx = 1

set @LastIndx =4000

set @length = 4000

set @start = 1
 

set @status = 0
 

while (@Indx >0)

begin
 

	set @TeamIn = '' 	

	if (datalength(@string)  > @start)

		set @TeamInSearch = @TeamInSearch + substring(@string, @start , @length)

	set @Indx = charindex('#',@TeamInSearch)

	if (@Indx = 0)

		begin

			set @TeamIn =  convert(nvarchar(4000),substring(@TeamInSearch, 1, datalength(@TeamInSearch) -  @Indx)) 

				

		end

			

			/*if(datalength(@TeamIn) >0)

				set @TeamInSearchb = @TeamInSearchb + substring(@TeamIn, @start , @length)

			set @Indx = charindex(':',@TeamInSearchb)

			if (@Indx = 0)

			begin

				set @TeamIn =  convert(nvarchar(4000),substring(@TeamInSearch, 1, datalength(@TeamInSearch) -  @Indx)) 

			end*/

						
 

			/*else 

				begin 	

					if (@Indx = 1)

					begin

						set @TeamIn =  '/' 

						set @TeamInSearch =  convert(nvarchar(4000),substring(@TeamInSearch, 4, datalength(@TeamInSearch) -  3)) 

					end

					else

						set @TeamIn =  convert(nvarchar(4000),substring(@TeamInSearch, 1, @Indx -1)) 

				end

	*/

	set @TeamInSearch = replace( @TeamInSearch, @TeamIn + '#', '')

	set @TeamIn = rtrim(ltrim(@TeamIn))

	set @length = @Indx  +1

	set @start =  @LastIndx

	set @LastIndx = @start + @length
 

	if (len(@TeamIn) >0)

		select @TeamIn as 'end'

	else 

		select 'No'

		/*begin

			insert into MultipleRefItems (ClientID, GroupID,ID, ReferenceID)

				select @ClientID, @groupID, @ID, @TeamIn
 

			SET @status = @status + @@ERROR

		end*/
 

end
 
 

-------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------
 
 

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

Open in new window

0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 22735544
First, you'll need a table of sequential numbers; I'll post the code to create that next.

Then, you can extract the strings with a single SELECT.

If you need to load them into variables, you'll need a table variable too, like so:


DECLARE @strings TABLE (
    id INT IDENTITY(1, 1),
    string VARCHAR(10)
    )

INSERT INTO @strings (string)
SELECT    
    RTRIM(LTRIM(SUBSTRING(string, seqNum + 1,
    CHARINDEX('#', string, seqNum + 1) - seqNum - 1))) AS value
FROM (  --yourTableNameHere
    SELECT '#524:458#526:457#' AS string
) AS sampleData
INNER JOIN seqNums ON
    seqNum <= LEN(string) - 1
    AND SUBSTRING(string, seqNum, 1) = '#'
ORDER BY seqNum

--SELECT * FROM @strings

DECLARE @a VARCHAR(10)
DECLARE @b VARCHAR(10)
SELECT
    @a = MAX(CASE WHEN id = 1 THEN string ELSE '' END),
    @b = MAX(CASE WHEN id = 2 THEN string ELSE '' END) --,
    --@c = ...
FROM @strings

SELECT @a AS string1, @b AS string2
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 22735551
DECLARE @maxValueToGenerate INT

-- chg next value to the highest value you need;
-- the current code supports up to 10M - 1, but you could increase that if you wanted to

SET @maxValueToGenerate = 8000

IF OBJECT_ID('seqNums') IS NOT NULL
    DROP TABLE seqNums

CREATE TABLE seqNums (
    seqNum INT,
    CONSTRAINT seqNums_CI --don't remove, improves performance!
        UNIQUE CLUSTERED (seqNum) WITH FILLFACTOR = 100
    )

INSERT INTO seqNums
SELECT [1s] + [10s] + [100s] + [1000s] + [10Ks] + [100Ks] +
      [1Ms] + [10Ms]
FROM (
    SELECT 0 AS [1s] UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL
    SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
    SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) AS digits
CROSS JOIN (
    SELECT 00 AS [10s] UNION ALL SELECT 10 UNION ALL SELECT 20 UNION ALL
    SELECT 30 UNION ALL SELECT 40 UNION ALL SELECT 50 UNION ALL SELECT 60 UNION ALL
    SELECT 70 UNION ALL SELECT 80 UNION ALL SELECT 90
) AS [10s]
CROSS JOIN (
      SELECT [100s]
      FROM (
    SELECT 000 AS [100s] UNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL
    SELECT 300 UNION ALL SELECT 400 UNION ALL SELECT 500 UNION ALL SELECT 600 UNION ALL
    SELECT 700 UNION ALL SELECT 800 UNION ALL SELECT 900
      ) inlineData
      WHERE [100s] <= @maxValueToGenerate
) AS [100s]
CROSS JOIN (
      SELECT [1000s]
      FROM (
    SELECT 0000 AS [1000s] UNION ALL SELECT 1000 UNION ALL SELECT 2000 UNION ALL
    SELECT 3000 UNION ALL SELECT 4000 UNION ALL SELECT 5000 UNION ALL SELECT 6000 UNION ALL
    SELECT 7000 UNION ALL SELECT 8000 UNION ALL SELECT 9000
      ) inlineData
      WHERE [1000s] <= @maxValueToGenerate
) AS [1000s]
CROSS JOIN (
      SELECT [10Ks]
      FROM (
    SELECT 00000 AS [10Ks] UNION ALL SELECT 10000 UNION ALL SELECT 20000 UNION ALL
    SELECT 30000 UNION ALL SELECT 40000 UNION ALL SELECT 50000 UNION ALL SELECT 60000 UNION ALL
    SELECT 70000 UNION ALL SELECT 80000 UNION ALL SELECT 90000
      ) inlineData
      WHERE [10Ks] <= @maxValueToGenerate
) AS [10Ks]
CROSS JOIN (
      SELECT [100Ks]
      FROM (
    SELECT 000000 AS [100Ks] UNION ALL SELECT 100000 UNION ALL SELECT 200000 UNION ALL
    SELECT 300000 UNION ALL SELECT 400000 UNION ALL SELECT 500000 UNION ALL SELECT 600000 UNION ALL
    SELECT 700000 UNION ALL SELECT 800000 UNION ALL SELECT 900000
      ) inlineData
      WHERE [100Ks] <= @maxValueToGenerate
) AS [100Ks]
CROSS JOIN (
      SELECT [1Ms]
      FROM (
    SELECT 0000000 AS [1Ms] UNION ALL SELECT 1000000 UNION ALL SELECT 2000000 UNION ALL
    SELECT 3000000 UNION ALL SELECT 4000000 UNION ALL SELECT 5000000 UNION ALL SELECT 6000000 UNION ALL
    SELECT 7000000 UNION ALL SELECT 8000000 UNION ALL SELECT 9000000
      ) inlineData
      WHERE [1Ms] <= @maxValueToGenerate
) AS [1Ms]
CROSS JOIN (
      SELECT [10Ms]
      FROM (
    SELECT 00000000 AS [10Ms] UNION ALL SELECT 10000000 UNION ALL SELECT 20000000 UNION ALL
    SELECT 30000000 UNION ALL SELECT 40000000 UNION ALL SELECT 50000000 UNION ALL SELECT 60000000 UNION ALL
    SELECT 70000000 UNION ALL SELECT 80000000 UNION ALL SELECT 90000000
      ) inlineData
      WHERE [10Ms] <= @maxValueToGenerate
) AS [10Ms]
WHERE [1s] + [10s] + [100s] + [1000s] + [10Ks] + [100Ks] + [1Ms] + [10Ms] <= @maxValueToGenerate
ORDER BY [1s] + [10s] + [100s] + [1000s] + [10Ks] + [100Ks] + [1Ms] + [10Ms]

DBCC SHOWCONTIG(seqNums) --make sure table is highly contig for max performance

SELECT COUNT(*) FROM seqNums --verify that the expected number of rows were generated
0
 

Author Closing Comment

by:caoimhincryan
ID: 31506675
Thanks a lot, thats brilliant. Works great :)
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now