Comma delimited 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)

Comma delimited values

Author

Topic

josethegeek
Starting Member

45 Posts

Posted - 2006-04-03 : 20:03:29

I want to let my members list their favorite music artist, comma seperated and then have those values linked to a search page that list other members who are also fans of set artist. Just like MySpace does it. Now my question is, what is the best way to save these values. I can just save them to a TEXT field, or seperate each value and add it to a different table that holds the MemberID and the artist they've entered. What would be the best way and what would be the least resource intensive?

Thanks!

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2006-04-03 : 22:42:05

The second way. Otherwise you would be asking for help at CSV TEAM.com

Think about how you would process that information in the future. You'd be creating a new thread "I have lots of values separated by commas. How do I separate them out and query against them".

Oh, and use the artist ID not name.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-04 : 01:48:18

Well, Just an Idea..

You can save them in one field with some seperated delimeters like '@' or ',' etc , but trying using varchar datatype with its maximum length of 8000 chars...

then to get the results in the row wise you can use the following functions which crack the columns values to the rows..


CREATE FUNCTION [dbo].[CrackInRows] (@delim varchar(1), @Phrase2Crack as varchar(8000))
RETURNS @CrackRow table 
(
	INROWS varchar(1000)
)
as
BEGIN 
insert @CrackRow
Select NullIf(SubString(@Delim + @Phrase2Crack + @Delim , IDNos , 
CharIndex(@Delim , @Delim + @Phrase2Crack + @Delim , IDNos) - IDNos) , '') AS INROW
FROM IDNos
WHERE IDNos <= Len(@Delim + @Phrase2Crack + @Delim) AND 
SubString(@Delim + @Phrase2Crack + @Delim , IDNos - 1, 1) = @Delim 
AND CharIndex(@Delim , @Delim + @Phrase2Crack + @Delim , IDNos) - IDNos > 0
return
END

So using In Clause you can find the values which are searched..

Hope this helps you

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-04 : 02:08:33

Refer this
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

Also refer where in @MYCSV here
http://sqlteam.com/forums/topic.asp?TOPIC_ID=55210

Madhivanan

Failing to plan is Planning to fail

josethegeek
Starting Member

45 Posts

Posted - 2006-04-04 : 16:14:11

Thanks you to the ones that replied. That is what I had in mind. I just wanted to make sure that it was the best way of doing it.

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources