fn to split concatenated and delimited string into seperate columns

[techytechy]

Hi guys,

Need a function to split concatenated and delimited string into seperate columns

Input to the function is one string like:

Fn('code1-Region1, code1-Region2, code2-region1, .....')

in the above Concatenation is '-' and delimiter is ','

Need output as

OutputTable1
--------------------
codecol
-----------
code1
code2
....
....
---------------------

OutputTable2
----------------------
Regioncol
-----------
Region1
Region2
....
....
-----------------------

Thanks

 

[gmmastros]

What version of SQL Server?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[techytechy]

sql server 2005

 

[markros]

See this function, for example:

USE [AllTests]
GO

/****** Object:  UserDefinedFunction [dbo].[fnSplit]    Script Date: 01/09/2010 19:07:49 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- Test query
CREATE FUNCTION [dbo].[fnSplit]
(@list  VARCHAR(8000),
 @delim CHAR(1) = ','
) RETURNS TABLE AS
RETURN
   WITH csvtbl(START, stop) AS (
     SELECT START = 1,
            stop = CHARINDEX(@delim COLLATE Slovenian_BIN2, @list + @delim)
     UNION ALL
     SELECT START = stop + 1,
            stop = CHARINDEX(@delim COLLATE Slovenian_BIN2,
                             @list + @delim, stop + 1)
     FROM   csvtbl
     WHERE  stop > 0
  )
  SELECT LTRIM(RTRIM(SUBSTRING(@list, START,
                      CASE WHEN stop > 0 THEN stop - START ELSE 0 END)))
         AS VALUE
  FROM   csvtbl
  WHERE  stop > 0
GO

For more details take also a look to comments to this blog

http://www.berezniker.com/content/pages/sql/microsoft-sql-server/alines-udf-string-split

 

[techytechy]

Tried the function with the params below. Doesnt Work. My parameter will be passed exactly as in below. Its one string parameter

select * from fn_split('code1-Region1, code2-region2')

Appreciate your further help

Thanks

 

[markros]

I changed my mind - see my answer to you in your MSDN thread. It may be better to run a custom function.

 

[TysonLPrice]

Here something I use from off the web that you can convert:

ALTER PROCEDURE [dbo].[rptConsolidatedCalcSheets]
(
 @pkGroupEmployer VarChar(2000)
)

/********************************************************************************************************

Desc Stored Procedure to print the calc sheets for GroupRating invitations.

This stored procedure consolidates the stored procedures:

rptCalcSheetbyGroup
rptCalcSheetbyPolicy
rptCalcSheetbyGroupEmployer

For use in Invitations.vb in the RateNet (vb .Net) project called from Rate (VB 6.0 GroupRating project)

**********************************************************************************************************/
AS
Set nocount on

declare @IDListPosition int 
declare @ArrValue varchar(2000) 
declare @TableVar table ( pkGroupEmployer varchar(50) NOT NULL ) 
set @pkGroupEmployer = COALESCE(@pkGroupEmployer ,'') 
IF @pkGroupEmployer <> '' 
BEGIN 
	set @pkGroupEmployer = @pkGroupEmployer + ',' 
	while patindex('%,%' , @pkGroupEmployer ) <> 0 
		begin 
			select @IDListPosition = patindex('%,%' , @pkGroupEmployer) 
			select @ArrValue = left(@pkGroupEmployer, @IDListPosition - 1) 
			INSERT INTO @TableVar (pkGroupEmployer) VALUES (@ArrValue) 
			select @pkGroupEmployer = stuff(@pkGroupEmployer, 1, @IDListPosition, '') 
		END 
END