SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 WHERE IN @MyCSV
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

ValterBorges
Flowing Fount of Yak Knowledge

USA
1429 Posts

Posted - 05/01/2003 :  11:51:31  Show Profile  Reply with Quote
Try using a Replace for replacing commas , with ','

either in T-SQL or in your client application where you're passing the string.

otherwise it's like jeff said you'll be looking for 1 string instead of many possible strings.




Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 05/01/2003 :  11:54:36  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
But that only works with either dynamic SQL or running sql directly from another app on the server.

the replace , with ',' doesn't work for:

select * from table where Field IN (@Variable)

(Brett: You might have missed my last post on the 1st page of this thread -- check it out for some SQL you run which should help clarify things, which you can play with)

- Jeff

Edited by - jsmith8858 on 05/01/2003 12:28:38
Go to Top of Page

ValterBorges
Flowing Fount of Yak Knowledge

USA
1429 Posts

Posted - 05/01/2003 :  14:43:55  Show Profile  Reply with Quote
Jeff I agree and understand completely.
I should have been more clear.

By the way my personal choice would be to go with the udf.




Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 05/01/2003 :  15:48:11  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
As my girlfriend's mother would say:

"the CSV-to-table UDF? What's not to like? It's like butta."


- Jeff
Go to Top of Page

SamC
White Water Yakist

USA
3459 Posts

Posted - 05/01/2003 :  23:47:08  Show Profile  Reply with Quote
Thanks everyone for the feedback. After reading all the references I see this isn't a new topic for many, but it was new to me.

This UDF provides a way of updating my stored procedures to allow generating reports on a "Group of courses" as opposed to a single course. Never needed the functionality before, but it seems like a straightforward approach to solving my problem. Without it, I would need to add some kind of Grouping ID column with broader development consequences. (read: more development time)

Next on my list: Changing VARCHAR to NVARCHAR to support Japanese course names. If I stumble, there may be a cry for help in this forum.

Sam


Go to Top of Page

ValterBorges
Flowing Fount of Yak Knowledge

USA
1429 Posts

Posted - 05/02/2003 :  09:29:41  Show Profile  Reply with Quote
Next on my list: Changing VARCHAR to NVARCHAR to support Japanese course names. If I stumble, there may be a cry for help in this forum.

Should be smooth on sql server.
Change Data Types.
Change Strings to have N in front indicating unicode string.
Maybe change some collation settings.

However what are you going to use to display the chinese characters?
I've had trouble with ie and netscape before don't know if they have gotten better.



Go to Top of Page

SamC
White Water Yakist

USA
3459 Posts

Posted - 05/02/2003 :  09:52:01  Show Profile  Reply with Quote
I'm setting up my Win XP station for Asian language support this AM.

IF I can get the characters into SQL 2000, they should be returned to ASP pages which render the characters in Netscape and IE. ( Fun ? - only if it works. )

Sam

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 03/27/2007 :  07:09:43  Show Profile  Visit SwePeso's Homepage  Reply with Quote
SQL Server 2005 style...
-- Prepare sample data
DECLARE	@Sample TABLE (ID INT, Code VARCHAR(3))

INSERT	@Sample
SELECT	290780, 'LT' UNION ALL
SELECT	290780, 'AY' UNION ALL
SELECT	290781, 'ILS' UNION ALL
SELECT	290780, 'AY'

-- Show the expected output
SELECT DISTINCT	s1.ID,
		STUFF((SELECT DISTINCT TOP 100 PERCENT ',' + s2.CODE FROM @Sample AS s2 WHERE s2.ID = s1.ID ORDER BY ',' + s2.CODE FOR XML PATH('')), 1, 1, '') AS CODES
FROM		@Sample AS s1
ORDER BY	s1.ID

SELECT DISTINCT	s1.ID,
		STUFF((SELECT TOP 100 PERCENT ',' + s2.CODE FROM @Sample AS s2 WHERE s2.ID = s1.ID ORDER BY ',' + s2.CODE FOR XML PATH('')), 1, 1, '') AS CODES
FROM		@Sample AS s1
ORDER BY	s1.ID

SELECT DISTINCT	s1.ID,
		STUFF((SELECT ',' + s2.CODE FROM @Sample AS s2 WHERE s2.ID = s1.ID FOR XML PATH('')), 1, 1, '') AS CODES
FROM		@Sample AS s1
ORDER BY	s1.ID

Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 03/27/2007 09:11:13
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 03/27/2007 :  09:48:42  Show Profile  Reply with Quote
Man, how did you find that one?

It's been almost 4 years...4 years? Oh good lord





Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 03/27/2007 :  09:54:21  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Khtan linked to this page some day ago
And I started to wonder if there could be an "inline" style to do this, and maybe even faster...


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17440 Posts

Posted - 03/27/2007 :  10:13:10  Show Profile  Reply with Quote
oh my fault ?


KH

Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 03/27/2007 :  11:31:12  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
quote:
Originally posted by Peso

SQL Server 2005 style...
-- Prepare sample data
DECLARE	@Sample TABLE (ID INT, Code VARCHAR(3))

INSERT	@Sample
SELECT	290780, 'LT' UNION ALL
SELECT	290780, 'AY' UNION ALL
SELECT	290781, 'ILS' UNION ALL
SELECT	290780, 'AY'

-- Show the expected output
SELECT DISTINCT	s1.ID,
		STUFF((SELECT DISTINCT TOP 100 PERCENT ',' + s2.CODE FROM @Sample AS s2 WHERE s2.ID = s1.ID ORDER BY ',' + s2.CODE FOR XML PATH('')), 1, 1, '') AS CODES
FROM		@Sample AS s1
ORDER BY	s1.ID

SELECT DISTINCT	s1.ID,
		STUFF((SELECT TOP 100 PERCENT ',' + s2.CODE FROM @Sample AS s2 WHERE s2.ID = s1.ID ORDER BY ',' + s2.CODE FOR XML PATH('')), 1, 1, '') AS CODES
FROM		@Sample AS s1
ORDER BY	s1.ID

SELECT DISTINCT	s1.ID,
		STUFF((SELECT ',' + s2.CODE FROM @Sample AS s2 WHERE s2.ID = s1.ID FOR XML PATH('')), 1, 1, '') AS CODES
FROM		@Sample AS s1
ORDER BY	s1.ID

Peter Larsson
Helsingborg, Sweden



I don't get it ... what is this doing? Isn't this thread about taking a CSV string and returning a table? Isn't this doing the opposite?

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 03/27/2007 :  11:34:03  Show Profile  Visit SwePeso's Homepage  Reply with Quote
I followed the wrong link? Ooops...


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 03/27/2007 :  11:39:43  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
it's still pretty cool, though !

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 03/27/2007 :  11:41:28  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Thanks. I will put it in the Library section.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SamC
White Water Yakist

USA
3459 Posts

Posted - 03/27/2007 :  11:48:37  Show Profile  Reply with Quote
Good threads improve with age... like a fine wine...
Go to Top of Page

ergen
Starting Member

Poland
5 Posts

Posted - 05/18/2009 :  05:26:28  Show Profile  Visit ergen's Homepage  Reply with Quote
here http://www.jacek-szarapa.com/index.php?p=sql&d=2 you can find another approach to the problem CSV table. There is no need to use loop to solve the problem, but it requires MS SQL 2005 or newer.

----------------
Jacek Szarapa
http://www.jacek-szarapa.com
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000