Home
  Home
Home
Search
Articles
Page Tag-Cloud
  Software
Software Tag-Cloud
Submit Patch
Building from Source
Open Source Definition
  Popular Tags
C Plus Plus
Source Code
Legacy
Class
Console
  Members
Login
Web-Email
Notable Members
  Official
Our Company
Copyright Information
Software EULA
GPL EULA
LGPL Eula
Pre-Release EULA
Privacy Policy
  Support
Make Contact
 
 
Split Array

SplitArray is a powerful table SQL function which splits delimited values into a row-set. One major use of this function is to use it to split a comma separated Ids which are passed into a stored procedure. Enjoy!


--Example usage:
SELECT
	*
FROM
	dbo.SplitArray('Jay,Steve,Novella,Bob,Burnside,James,Randi,Rebecca,Watson', ',')


Example output:
Bob
Burnside
James
Jay
Novella
Randi
Rebecca
Steve
Watson

 CREATE FUNCTION SplitArray
(
	@String VarChar(Max),
	@Delimiter VarChar(100) = ','
)
RETURNS @Array TABLE  
(
	Value VarChar(Max)
)
AS
BEGIN
	DECLARE @LastIndex Int
	DECLARE @Index Int
	DECLARE @Length Int

	DECLARE @TmpArray TABLE  
	(
		Value VarChar(Max)
	)

	IF @String IS NOT NULL
	BEGIN--IF
		-------------------------------------------------------------------------------------------

		WHILE(IsNull(@Index, 1) > 0)
		BEGIN--WHILE
			-------------------------------------------------------------------------------------------
			SET @Index = CharIndex(@Delimiter, @String, IsNull(@Index, 0) + 1)

			IF @Index > 0
			BEGIN--IF
				SET @Length = @Index - IsNull(@LastIndex, 0) - 1
			END ELSE BEGIN--IF
				SET @Length = LEN(@String) - IsNull(@LastIndex, 0)
			END--IF

			INSERT INTO @TmpArray(Value)
			SELECT LTrim(RTrim(SubString(@String, IsNull(@LastIndex, 0) + 1, @Length)))

			SET @LastIndex = @Index + LEN(@Delimiter) - 1
			-------------------------------------------------------------------------------------------
		END--WHILE

		-------------------------------------------------------------------------------------------
	END--IF

	INSERT INTO @Array(Value) SELECT DISTINCT * FROM @TmpArray
	RETURN
END


Is this code snippet, product or advice warrantied against ill-effect and/or technical malaise? No. No it's not! Not expressed - Not implied - not at all.



Tags:
 Function    Rowset    SQL Server    String  

Created by Josh Patterson on 2/9/2013, last modified by Josh Patterson on 2/17/2013

No comments currently exists for this page. Why don't you add one?
First Previous Next Last 

Login or signup to leave a comment.
 
Copyright © 2017 NetworkDLS.
All rights reserved.
 
Privacy Policy | Our Company | Contact