?
Solved

SQL Server 2000 - Spliting up a string

Posted on 2008-10-16
8
Medium Priority
?
339 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 
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
 

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:Scott Pletcher
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:
Scott Pletcher earned 2000 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

771 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