Parse Values to Separate Columns

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

Transact-SQL (2005)

Parse Values to Separate Columns

Author

Topic

SCHEMA
Posting Yak Master

192 Posts

Posted - 2009-06-26 : 19:39:19

I want Separate all Below Values to Separate Column. How Do I parse these?

"0098G ","A UNI ","000 ","06/25/2008"," 1,150.00","",""

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-06-26 : 19:45:08

Are all these values being passed as a single string, with the double quotes and commas?

Some days you're the dog, and some days you're the fire hydrant.

SCHEMA
Posting Yak Master

192 Posts

Posted - 2009-06-26 : 19:53:28

Yes, I need to parse it.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-26 : 20:16:49

see
fnParseString

KH
[spoiler]Time is always against us[/spoiler]

SCHEMA
Posting Yak Master

192 Posts

Posted - 2009-06-26 : 22:08:04

How I can I use it to get:

Col1 Col2 col3 Col4 Col5
0098G A UNI 000 06/25/2008 1150.00

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-26 : 22:41:05

[code]
select dbo.fnParseString(-1, ',', col) as Col1,
dbo.fnParseString(-2, ',', col) as Col2,
. . .
[/code]

KH
[spoiler]Time is always against us[/spoiler]

SCHEMA
Posting Yak Master

192 Posts

Posted - 2009-06-27 : 00:11:44

Thanks But fnParseString is not working for : It doesn't work after 7th column ? Does it have defect?

My data is like this:

"4130 ","413G ","155","Hoff Palaces ","RDA017231A ","2008-4174E","09/01/2007","08/31/2005"," 20.00"," .00"," .00"," 20.00","O","03/31/2009",""

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-27 : 00:21:04

[code]
declare @str varchar(max)
declare @i int

select @str = '"4130 ","413G ","155","Hoff Palaces ","RDA017231A ","2008-4174E","09/01/2007","08/31/2005"," 20.00"," .00"," .00"," 20.00","O","03/31/2009",""'
select @i = 1
while (@i > -15)
begin
print dbo.fnParseString(-@i, ',', @str)
select @i = @i - 1
end

RESULT:
"4130 "

""
"03/31/2009"
"O"
" 20.00"
" .00"
" .00"
" 20.00"
"08/31/2005"
"09/01/2007"
"2008-4174E"
"RDA017231A "
"Hoff Palaces "
"155"
"413G "

*/

KH
[spoiler]Time is always against us[/spoiler]

SCHEMA
Posting Yak Master

192 Posts

Posted - 2009-06-27 : 00:29:07

This is how My Value looks like.
It stops working after 9th column,

Thanks a lot.

Please try this:

declare	@var varchar(200)

select	@var = '"4130 ","413G   ","155","Hoff Palaces                                                    ","RDA017231A          ","2008-4174E","09/01/2007","08/31/2005","                   20.00","                 .00","                   .00","                        20.00","O","03/31/2009",""'                   
select 
	dbo.fnParseString(-2, '","', @var),
	dbo.fnParseString(-4, '","', @var),
    dbo.fnParseString(-6, '","', @var),
    dbo.fnParseString(-8, '","', @var),
    dbo.fnParseString(-10, '","', @var),
    dbo.fnParseString(-12, '","', @var),
    dbo.fnParseString(-14, '","', @var),
    dbo.fnParseString(-16, '","', @var),
    dbo.fnParseString(-18, '","', @var),
    dbo.fnParseString(-20, '","', @var),
    dbo.fnParseString(-22, '","', @var),
    dbo.fnParseString(-24, '","', @var),
    dbo.fnParseString(-26, '","', @var),
    dbo.fnParseString(-28, '","', @var),
    dbo.fnParseString(-30, '","', @var)

SCHEMA
Posting Yak Master

192 Posts

Posted - 2009-06-27 : 01:09:32

Someone Help!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-27 : 01:26:31

try this too


SELECT f.Val
FROM dbo.ParseValues(Yourstring,',')


ParseValues can be found here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=128201

SCHEMA
Posting Yak Master

192 Posts

Posted - 2009-06-27 : 01:30:50

It didn't work. Dbo.fnParseString Works but it is not working in this scenereio.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-27 : 01:39:23

quote:
Originally posted by SCHEMA

It didn't work. Dbo.fnParseString Works but it is not working in this scenereio.

why it didnt work? what was error?

SCHEMA
Posting Yak Master

192 Posts

Posted - 2009-06-27 : 01:51:39

I want the values in Separate Columns.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-27 : 01:54:56

oh..in that case you can cross tab this to get your result.use logic below for cross tabing the result obtained

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

SCHEMA
Posting Yak Master

192 Posts

Posted - 2009-06-27 : 02:07:26

Maybe Peso can answer this.

ramireddy
Starting Member

4 Posts

Posted - 2009-06-27 : 03:14:48

declare @sql nvarchar(max)
set @sql = '"4130 ","413G ","155","Hoff Palaces ","RDA017231A ","2008-4174E","09/01/2007","08/31/2005"," 20.00"," .00"," .00"," 20.00","O","03/31/2009",""'
declare @execsql nvarchar(max)
declare @wordscount int
set @wordscount = (len(@sql) - len(replace(@sql,',','')))
;with cte(rn ) as
(
select 1 as rn
union all
select rn + 1 as rn
from cte where rn < @wordscount
)
select rn into #temp from cte
declare @Columns nvarchar(max)
set @Columns = stuff((select ',[' + cast(rn as varchar) + ']' from #temp for xml path('')),1,1,'')

set @execsql = 'select * from
( select * from fn_split(''' + @sql + ''','','') )p
pivot (max(value) for position in (' + @Columns + '))pvt'

exec(@execsql)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-27 : 03:20:56

The ORIGINAL sample data has a comma for one column; a value with a thousand separator.
You should use " as separator and only get even items, like this

DECLARE	@s VARCHAR(100)

SET	@s = '"0098G ","A UNI ","000 ","06/25/2008"," 1,150.00","",""'

SELECT	*
FROM	dbo.fnParseList('"', @s)
WHERE	RowID % 2 = 0

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-27 : 03:22:20

The "even" techinque also works for data which doesn't have a comma as thousand separator

DECLARE	@s VARCHAR(200)

SET	@s = '"4130 ","413G ","155","Hoff Palaces ","RDA017231A ","2008-4174E","09/01/2007","08/31/2005"," 20.00"," .00"," .00"," 20.00","O","03/31/2009",""'

SELECT	*
FROM	dbo.fnParseList('"', @s)
WHERE	RowID % 2 = 0

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-27 : 03:26:03

quote:
Originally posted by SCHEMA

declare @var varchar(200)

select @var = '"4130 ","413G ","155","Hoff Palaces ","RDA017231A ","2008-4174E","09/01/2007","08/31/2005"," 20.00"," .00"," .00"," 20.00","O","03/31/2009",""'

No wonder.
Your string is almost 300 characters long, and you try to cram that into a 200 byte variable.
Here the data gets truncated when inserted into the variable.

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-27 : 03:28:48

Good catch

KH
[spoiler]Time is always against us[/spoiler]

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources