CSV Values - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

CSV Values

Author

Topic

daidaluus
Yak Posting Veteran

73 Posts

Posted - 2008-11-20 : 02:55:23

I have read the Rob's nice article "Parsing CSV Values Into Multiple Rows" and used it several times in my T-SQL developments. My question is what if I want to gain a two-columns table from a CSV. For example suppose I have the following string:

<Value1_1,Value2_1><Value1_2,Value2_2><Value1_3,Value2_3>

Now I want to get the following result

Col1 Col2
---- ----
Value1_1 Value2_1
Value1_2 Value2_2
Value1_3 Value2_3

How can I get this result?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-20 : 03:10:00

is <Value1_1,Value2_1><Value1_2,Value2_2><Value1_3,Value2_3>
existing in single row or as different rows? if single row, whats the delimiter?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-20 : 03:18:49

SELECT *, REPLACE(data, ',', ' ')
FROM dbo.fnParseList('><', '>' + @param + '<')

E 12°55'05.63"
N 56°04'39.26"

daidaluus
Yak Posting Veteran

73 Posts

Posted - 2008-11-20 : 03:36:23

Let me tell you with an example:

DECLARE @Phrase varchar(8000)

SET @Phrase = 'Value1,Value2,Value3,Value4'

SELECT
NullIf(SubString(',' + @Phrase + ',' , ID , CharIndex(',' , ',' + @Phrase + ',' , ID) - ID) , '') AS Word
FROM Tally
WHERE ID <= Len(',' + @Phrase + ',') AND SubString(',' + @Phrase + ',' , ID - 1, 1) = ','

It will return:

Word
------
Value1
Value2
Value3
Value4

Now I want to get

Word1 Word2
------ -----
Value1 Value2
Value3 Value4

The delimiter is still comma but i want to seperate each couple in a single row, as if it is a two-columns table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-20 : 03:45:48

[code]DECLARE @Phrase varchar(8000)

SET @Phrase = 'Value1,Value2,Value3,Value4'

SELECT t1.Val,t2.Val
FROM
(SELECT ID,Val FROM dbo.ParseValues(@Phrase,',') WHERE ID %2<>0)t1
JOIN (SELECT ID,Val FROM dbo.ParseValues(@Phrase,',') WHERE ID %2=0)t2
ON t1.ID=t2.ID-1

output
------------------------
Val1 Val2
-----------------------
Value1 Value2
Value3 Value4[/code]

daidaluus
Yak Posting Veteran

73 Posts

Posted - 2008-11-20 : 04:02:31

Thank you for your reply. but i dont have the ID field. is there any direct solution?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-20 : 04:08:00

The ID column is returned from ParseValues function.

E 12°55'05.63"
N 56°04'39.26"

daidaluus
Yak Posting Veteran

73 Posts

Posted - 2008-11-20 : 04:18:41

I know but I had to modify that funtion and there is no ID in it. and i'm looking for a way to solve this problem from the scratch. if there is any way to it. i tried to find one based on ParseValues function. i modified it in a few ways but i couldnt get the desired result. Can you guys help me?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-20 : 04:23:22

[code]DECLARE @Phrase varchar(8000)

SET @Phrase = 'Value1,Value2,Value3,Value4'

SELECT MAX(CASE WHEN col = 1 THEN data ELSE NULL END),
MAX(CASE WHEN col = 0 THEN data ELSE NULL END)
FROM (
SELECT (rowID - 1) / 2 AS row,
rowID % 2 AS col,
data
FROM dbo.fnParseList(',', @Phrase)
) AS d
GROUP BY row
ORDER BY row[/code]
dbo.fnParseList is found here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

E 12°55'05.63"
N 56°04'39.26"

daidaluus
Yak Posting Veteran

73 Posts

Posted - 2008-11-20 : 04:45:35

according to Rob's article i wrote this function:

CREATE FUNCTION ParseValues(@String varchar(8000), @Seperator char(1))
RETURNS TABLE
AS
RETURN (
SELECT SUBSTRING(@Seperator + @String + @Seperator, Id,
CHARINDEX(@Seperator, @Seperator + @String + @Seperator, Id) - Id) phrase
FROM Tally
WHERE LEN(@Seperator + @String + @Seperator) >= Id
AND SUBSTRING(@Seperator + @String + @Seperator, Id - 1, 1) = @Seperator
AND CHARINDEX(@Seperator, @Seperator + @String + @Seperator, Id) - Id > 0
)

I want something like ExtendedParseValues to get:
DECLARE @Phrase varchar(8000)

SET @Phrase = 'Value1,Value2,Value3,Value4'

SELECT *
FROM dbo.ExtendedParseValues(@Phrase, ',')

and the result should be:

Phrase1 Phrase2
-----------------------
Value1 Value2
Value3 Value4

you can find the article here: http://www.sqlteam.com/article/parsing-csv-values-into-multiple-rows

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-20 : 05:49:43

quote:

Originally posted by visakh16

DECLARE @Phrase varchar(8000)

SET @Phrase = 'Value1,Value2,Value3,Value4'

SELECT t1.Val,t2.Val
FROM
(SELECT ID,Val FROM dbo.ParseValues(@Phrase,',') WHERE ID %2<>0)t1
JOIN (SELECT ID,Val FROM dbo.ParseValues(@Phrase,',') WHERE ID %2=0)t2
ON t1.ID=t2.ID-1


output
------------------------
Val1	Val2
-----------------------
Value1	Value2
Value3	Value4

forgot to post link for ParseValues i used

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=113563

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources