comma deliminated column - 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 2005 Forums

Transact-SQL (2005)

comma deliminated column

Author

Topic

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2010-03-09 : 00:58:41

How to get value from comma deliminated column.

eg. get first element from col1 (2,5,5)

tks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-09 : 01:04:14

make use of fnParseString

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

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2010-03-09 : 01:19:45

I already had Split function which accepts ValueList and deliminator and returns table
Eg.
1
2
3

How can I get frist value , second value and third value ?

Thanks.

quote:
Originally posted by khtan

make use of fnParseString

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-09 : 01:27:47

quote:
Originally posted by kwikwisi

I already had Split function which accepts ValueList and deliminator and returns table
Eg.
1
2
3

How can I get frist value , second value and third value ?

Thanks.

quote:
Originally posted by khtan

make use of fnParseString

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

In your function you can create a column that indicates row number

Madhivanan

Failing to plan is Planning to fail

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2010-03-09 : 01:30:05

Cannot access from outside ? coz that is done by other and cannot modify.

tks.

quote:
Originally posted by madhivanan

quote:
Originally posted by kwikwisi

I already had Split function which accepts ValueList and deliminator and returns table
Eg.
1
2
3

How can I get frist value , second value and third value ?

Thanks.

quote:
Originally posted by khtan

make use of fnParseString

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

In your function you can create a column that indicates row number

Madhivanan

Failing to plan is Planning to fail

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-03-09 : 01:48:22

Try something like this

select * from
(select *,ROW_NUMBER() over (order by column_name) as s_no from myfunction()) s
where s_no =1

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-09 : 04:27:24

that won't work for any list that isn't in strictly ascending order.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2010-03-10 : 22:34:26

If it is not in ascending order, how can i retrieve ?

I want something like
col1.row1 as a from function
col1.row2 as b from function

Pls ! I still havent sovled it :(

quote:
Originally posted by Transact Charlie

that won't work for any list that isn't in strictly ascending order.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-10 : 22:35:14

quote:
Originally posted by kwikwisi

I already had Split function which accepts ValueList and deliminator and returns table
Eg.
1
2
3

How can I get frist value , second value and third value ?

Thanks.

quote:
Originally posted by khtan

make use of fnParseString

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

Looks like you didn't check out that function. fnParseString does not return the element in row. You specify which element you want to extract.

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

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2010-03-10 : 22:48:47

Thanks.
Can u pls show me the eg.
Let say I want to extract the value from row2. How can I call that function ? I only know the col name and deliminator is ','.

quote:
Originally posted by khtan

quote:
Originally posted by kwikwisi

I already had Split function which accepts ValueList and deliminator and returns table
Eg.
1
2
3

How can I get frist value , second value and third value ?

Thanks.

quote:
Originally posted by khtan

make use of fnParseString

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

Looks like you didn't check out that function. fnParseString does not return the element in row. You specify which element you want to extract.

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-10 : 23:02:01

[code]
select dbo.fnParseString(-1, ',', '2,5,5'),
dbo.fnParseString(-2, ',', '2,5,5'),
dbo.fnParseString(-3, ',', '2,5,5')
[/code]

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

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2010-03-10 : 23:04:49

Yes.
Problem is I dont know the what is the string ('2,5,5').

quote:
Originally posted by khtan

select dbo.fnParseString(-1, ',', '2,5,5'),
dbo.fnParseString(-2, ',', '2,5,5'),
dbo.fnParseString(-3, ',', '2,5,5')

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-10 : 23:08:53

that's the data from your column


select	dbo.fnParseString(-1, ',', [your_col_name_that_contain_the_csv]),
	dbo.fnParseString(-2, ',', [your_col_name_that_contain_the_csv]),
	dbo.fnParseString(-3, ',', [your_col_name_that_contain_the_csv])
from    yourtable

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

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2010-03-10 : 23:10:15

SORRY !... I was wrongly thinking

quote:
Originally posted by khtan

that's the data from your column

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

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2010-03-11 : 01:56:56

Pls let me ask one more thing.

Is it possible to check ...
Does @value match one of the positions of comma separated values ?
Eg. @value = 3
20[position1]
40[position2]
60[position3]
70[position4]

tks.

quote:
Originally posted by madhivanan

quote:
Originally posted by kwikwisi

I already had Split function which accepts ValueList and deliminator and returns table
Eg.
1
2
3

How can I get frist value , second value and third value ?

Thanks.

quote:
Originally posted by khtan

make use of fnParseString

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

In your function you can create a column that indicates row number

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-11 : 02:09:23

check for exists() of


if   exists 
     (
       select *
       from   your_split_function ( < the CSV > )
       where  value = @value
     )
begin
     print 'match'
end

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

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2010-03-11 : 02:32:44

IF EXISTS(
select *
from DBO.SPLIT ('5,60,70,100',',')
where value = 5
)
BEGIN
print 'match'
END

ABOVE RETURNS match

IF EXISTS(
select *
from DBO.SPLIT ('5,60,70,100',',')
where value = 3
)
BEGIN
print 'match'
END

ABOVE RETURNS NOTHING , BUT SHOULD RETURN 'match' COZ '3' SHOULD BE ASSUMED AS POSITION NOT VALUE. [70 = position3]

quote:
Originally posted by khtan

check for exists() of

if exists
(
select *
from your_split_function ( < the CSV > )
where value = @value
)
begin
print 'match'
end

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-11 : 02:37:13

you want to match value or element position / row no ?

Does your split function also return the row no ? use that column instead

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-11 : 02:38:57

if your split function does not return the row no, you might want to consider using fnParseList

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

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2010-03-11 : 02:41:10

should match with row position and I need to know that position but Split fun doesnt return it. :(

quote:
Originally posted by khtan

you want to match value or element / row position ?

Does your split function also return the row position ? use that column instead

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

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2010-03-11 : 02:52:01

GREAT ! TKS.

quote:
Originally posted by khtan

if your split function does not return the row no, you might want to consider using fnParseList

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