my code stock.com

Function which returns a table based on a string delimited with unique separator.

Snippet options

Download: Download snippet as split-string-in-list.sql.
Copy snippet: For this you need a free my code stock.com account.
Embed code : You will find the embed code for this snippet at the end of the page, if you want to embed it into a website or a blog!

/****** Object:  UserDefinedFunction [reporting].[tvf_SplitStringInList]    Script Date: 04/11/2012 08:33:16 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


/* ***************************************************************************************************
 * Project Name:          	THOT
 * Project Version:      	$VersionInfo$
 * Creation Date:         	$Date$
 * Author:                	$Author$
 * Classes Name:          	$ClassName$
 * General Description:	$Description$ (don't forget to create MS_Description extended property)
 Function which returns a table based on a string delimited with unique separator.
 * References:            	$Reference$
 * ====================================================================================================
 * MODIFICATIONS
 * ----------------------------------------------------------------------------------------------------
 * Date            	Author        			Description			Project id
 * ----------------------------------------------------------------------------------------------------
 * Please complete after each code modification.
 * <date>          	<author>      			<fix id>			<documentation id>
 * <description of changes>
 * ************************************************************************************************* */

CREATE FUNCTION [dbo].[tvf_SplitStringInList] (@param nvarchar(MAX), @Delim char(1)= ',') 
RETURNS @Values TABLE ([Param] nvarchar(MAX), [RowNumber] int)
AS 
BEGIN 
      DECLARE @RowNumber INT
      DECLARE @chrind INT 
      DECLARE @Piece nvarchar(MAX) 
      SELECT @chrind = 1, @RowNumber = 0  
      WHILE @chrind > 0 
      BEGIN 
            SELECT @chrind = CHARINDEX(@Delim,@param) 
            IF @chrind  > 0 
                  SELECT @Piece = LEFT(@param,@chrind - 1) 
            ELSE 
                  SELECT @Piece = @param

            INSERT  @Values([Param], [RowNumber]) VALUES(@piece, @RowNumber) 
            SELECT @param = RIGHT(@param,LEN(@param) - @chrind) 
			SELECT @RowNumber = @RowNumber + 1

            IF LEN(@param) = 0 
                  BREAK 
      END 
RETURN 
END



GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Function which split a string based on a delimiter.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'tvf_SplitStringInList'
GO

Create a free my code stock.com account now.

my code stok.com is a free service, which allows you to save and manage code snippes of any kind and programming language. We provide many advantages for your daily work with code-snippets, also for your teamwork. Give it a try!

Find out more and register now

You can customize the height of iFrame-Codes as needed! You can find more infos in our API Reference for iframe Embeds.