| Author |
 Topic  |
|
ValterBorges
Flowing Fount of Yak Knowledge
USA
1429 Posts |
 Posted - 05/01/2003 : 11:51:31
|
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.
|
 |
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
Posted - 05/01/2003 : 11:54:36
|
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 |
|
|
|
ValterBorges
Flowing Fount of Yak Knowledge
USA
1429 Posts |
Posted - 05/01/2003 : 14:43:55
|
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.
|
|
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
Posted - 05/01/2003 : 15:48:11
|
As my girlfriend's mother would say:
"the CSV-to-table UDF? What's not to like? It's like butta."
- Jeff |
|
|
|
SamC
White Water Yakist
USA
3459 Posts |
Posted - 05/01/2003 : 23:47:08
|
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
|
|
|
|
ValterBorges
Flowing Fount of Yak Knowledge
USA
1429 Posts |
Posted - 05/02/2003 : 09:29:41
|
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.
|
|
|
|
SamC
White Water Yakist
USA
3459 Posts |
Posted - 05/02/2003 : 09:52:01
|
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
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 03/27/2007 : 07:09:43
|
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 |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 03/27/2007 : 09:54:21
|
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 |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 03/27/2007 : 10:13:10
|
oh my fault ?
KH
|
|
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
Posted - 03/27/2007 : 11:31:12
|
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
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 03/27/2007 : 11:34:03
|
I followed the wrong link? Ooops...
Peter Larsson
Helsingborg, Sweden |
|
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 03/27/2007 : 11:41:28
|
Thanks. I will put it in the Library section.
Peter Larsson
Helsingborg, Sweden |
|
|
|
SamC
White Water Yakist
USA
3459 Posts |
Posted - 03/27/2007 : 11:48:37
|
| Good threads improve with age... like a fine wine... |
|
|
|
ergen
Starting Member
Poland
5 Posts |
|
|
Topic |
|
WHERE IN @MyCSV
