Parse csv 2 times, - 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)

Parse csv 2 times,

Author

Topic

Naveed88
Starting Member

19 Posts

Posted - 2009-02-13 : 23:32:49

Hi,,,,

i have a function which does parsing string value into table column
ParseStrFromCSV(<parse char>, <csv>)
and it works fine....

now what i want to do is....

i have a huge string in scv, like
@csv = 'name,address,info1,inf2,|name,address,info1,inf2,
|name,address,info1,inf2,|'

and i converted it into a table by '|'
ParseStrFromCSV('|', @csv)
so i get table with 3 rows

name,address,info1,inf2
name,address,info1,inf2
name,address,info1,inf2

now I again want to break all records one by one and it should autometically take
each record and give me

name
address
info1
info2

for each record
so that i can insert these values in other tables

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-13 : 23:46:16

declare @str1 varchar(max)
set @str1= 'name,address,info1,inf2'

SELECT
replace(SUBSTRING(@str1,charindex(',',@str1,v.number),abs(charindex(',',@str1,charindex(',',@str1,v.number)+1)-charindex(',',@str1,v.number))),',','')as value
FROM master..spt_values AS v
WHERE v.Type = 'P'
AND v.number > 0
AND v.number <= len(@str1)
AND substring(',' + @str1, v.number, 1) = ','

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-02-13 : 23:53:25

[code]
declare @reports table(string varchar(600) )
insert into @reports
select 'name,address,info1,inf2'

SELECT
SUBSTRING(s.string, v.Number - 1,
COALESCE(NULLIF(CHARINDEX(',', s.string, v.Number), 0), LEN(s.string) + 1) - v.Number + 1) AS value
FROM @reports AS s
INNER JOIN master..spt_values AS v ON v.Type = 'p'
WHERE SUBSTRING(',_' + s.string, v.Number, 1) = ','

[/code]

Jai Krishna

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-14 : 00:24:53

use fnParseList(',', Col7) function for this
search this function in this link
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Splitting,delimited,lists

Naveed88
Starting Member

19 Posts

Posted - 2009-02-14 : 00:37:26

thanks 4 replay

it works But i want result in table

name Address info1 info2
name Address info1 info2
name Address info1 info2

and it gives
name
Address
info1
info2
name
Address
info1
info2name
Address
info1
info2

--
(¨`·.·´¨) Always
`·.¸(¨`·.·´¨) Keep
(¨`·.·´¨)¸.·´ Smiling!
`·.¸.·´ & Programming
Regards....
"Deevan" [Naveed Anjum]
Web Developer
9867374437-Mumbai.4

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-14 : 01:10:57

declare @str1 varchar(max)
set @str1= 'name,address,info1,inf5,|name,address,info1,inf6,
|name,address,info1,inf2,'

SELECT
replace(SUBSTRING(@str1,charindex('|',@str1,v.number),abs(charindex('|',@str1,charindex('|',@str1,v.number)+1)-charindex('|',@str1,v.number))),'|','')as value
FROM master..spt_values AS v
WHERE v.Type = 'P'
AND v.number > 0
AND v.number <= len(@str1)
AND substring('|' + @str1, v.number, 1) = '|'

Naveed88
Starting Member

19 Posts

Posted - 2009-02-14 : 02:10:06

quote:
Originally posted by bklr

declare @str1 varchar(max)
set @str1= 'name,address,info1,inf5,|name,address,info1,inf6,
|name,address,info1,inf2,'

SELECT
replace(SUBSTRING(@str1,charindex('|',@str1,v.number),abs(charindex('|',@str1,charindex('|',@str1,v.number)+1)-charindex('|',@str1,v.number))),'|','')as value
FROM master..spt_values AS v
WHERE v.Type = 'P'
AND v.number > 0
AND v.number <= len(@str1)
AND substring('|' + @str1, v.number, 1) = '|'

==================

all right let me simplify my question

Now i want column in row format

like
Table1
column
A
B
C
D

i want ...
Table2
col1 col2 col3 vol4 col5
A B C D E

--
(¨`·.·´¨) Always
`·.¸(¨`·.·´¨) Keep
(¨`·.·´¨)¸.·´ Smiling!
`·.¸.·´ & Programming
Regards....
"Deevan" [Naveed Anjum]
Web Developer
9867374437-Mumbai.4

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-14 : 02:14:31

use dynamic cross tab queries

in 2005 & above u can use pivot

Naveed88
Starting Member

19 Posts

Posted - 2009-02-14 : 02:18:37

quote:
Originally posted by bklr

use dynamic cross tab queries

in 2005 & above u can use pivot

Thanks for nice suggestion

All Helps are appreciated

--
(¨`·.·´¨) Always
`·.¸(¨`·.·´¨) Keep
(¨`·.·´¨)¸.·´ Smiling!
`·.¸.·´ & Programming
Regards....
"Deevan" [Naveed Anjum]
Web Developer
9867374437-Mumbai.4

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-14 : 02:22:01

ur welcome

see this it may be helpful to u
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources