Parse an Array

wsturdev
wsturdev used Ask the Experts™
on
I have a parameter coming into a SPROC.  The parameter is a varchar, but is made of an unpredictable Longs separated by a semi-colon, such as:

12345;67890;2345678;12;90444 (the actual number of values is unpredictable)

I need SQL Logic for my sproc that will parse that varchar and let me access each of those longs, something like:

Declare @tmpArray
SET @tmpArray = @incomingvarchar
For i = lowerbound(tmpArray) to upperbound(tmpArray) step 1
      @longval = tmpArray(i)
      Do stuff with @longval
next i
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Consultant
Top Expert 2009
Commented:
this will parse the string

use it like this

SELECT * FROM [dbo].[fn_ParseString]( '12345;67890;2345678;12;90444' )
CREATE FUNCTION [dbo].[fn_ParseString](
@String       TEXT )
RETURNS @tbl TABLE ( s INT)

AS
BEGIN
	DECLARE @i INT, @j INT
	SELECT       @i = 1
	WHILE @i <= DATALENGTH(@String)
	BEGIN
		SELECT      @j = CHARINDEX(';', @String, @i)
		IF @j = 0
		BEGIN
			  SELECT      @j = DATALENGTH(@String) + 1
		END
		INSERT      @tbl SELECT SUBSTRING(@String, @i, @j - @i)
		SELECT      @i = @j + 1
	END
	RETURN
END

Open in new window

Author

Commented:
Excellent!!  Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial