Solved

SQL Server 2000 - Spliting up a string

Posted on 2008-10-16
8
308 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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

706 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

16 Experts available now in Live!

Get 1:1 Help Now