Comma separated Values to Table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Comma separated Values to Table

Author

Topic

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2012-06-27 : 01:08:28

Hi,
Need Query to convert Comma separated values to Table. Were as my CSV values will be separated by Pipe symbol and comma with n numbers. It must be converted to two columns.

Ex:
Var='1|Row1,2|Row2,3|Row3,...,n|Rown

Output:
[COLUMN1] [COLUMN2]
1 Row1
2 Row2
3 Row3
. .
. .
n Rown

Help me to fix this one with Query.

Regards,
Kalai

Manigandan
Starting Member

4 Posts

Posted - 2012-06-27 : 03:20:33

IF EXISTS (SELECT * FROM Sys.Objects WHERE Name LIKE 'fnTableFromCSV' AND Type = 'TF')
DROP FUNCTION fnTableFromCSV
GO
-- =============================================
-- Author: <Mani>
-- Created Date: <Nov 29 2010>
-- Description: This function using for split the spcified string
-- =============================================
CREATE FUNCTION [dbo].[fnTableFromCSV]
(
@theString VARCHAR(MAX)
, @separator CHAR(1)
)
RETURNS @Items TABLE (Items VARCHAR(MAX))
AS
BEGIN
DECLARE @seppos INT
SET @theString = @theString + @separator
WHILE PATINDEX('%' + @separator + '%' , @theString) <> 0
BEGIN
SELECT @seppos = PATINDEX('%' + @separator + '%' , @theString)
INSERT INTO @Items
SELECT LEFT(@theString, @seppos - 1)
SELECT @theString = STUFF(@theString, 1, @seppos, '')
END
RETURN
END
GO

DECLARE @Var VARCHAR(1000) = '1|Row1,2|Row2,3|Row3'
SELECT LEFT(Items,CHARINDEX(',',Items)-1) AS COLUMN1
, SUBSTRING(Items,CHARINDEX(',',Items)+1, LEN(Items)) AS COLUMN2
FROM [dbo].[fnTableFromCSV](REPLACE(REPLACE(REPLACE(@Var,'|',';'),',','|'),';',','),'|')
GO
--Output:
--[COLUMN1] [COLUMN2]
--1 Row1
--2 Row2
--3 Row3

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources