Hey Cetin, ne var ne yok?
This is a VFP app with remote views that I'm converting for someone I'm mentoring and clients may or may not have clr installed. The UDF Sergey post on his website (ufn_mline) is working very well ( haven't pushed it for speed yet but in this app shouldn't be a problem)
(I'm using VB in .net and loving the whole .net experience - going to Texas in Feb for a strataframe class - very VFE gibi )
I can see in the future though that creating CLR UDFs is going to make a lot of things in SQL Server more fun.
Oh, since I'm about to post this as a question in another thread, how do I pass a string of strings as a param to get the equivalent of VFPs INLIST. ( again, a remote view and I'm building a dynamic view parameter ) Can't use a correlated subquery in this case but I have a list of values
SELECT * from jobs where INLIST(jobid,'00555','00444','00333')
In T sql SELECT * from jobs where jobid IN ( and here I want something like ['00555','00444','00333'] )
Sagol
>>In a memo field I would use mline to retrieve rows in a sql select.
>>
>>Now I need to obtain the first 3 rows of a varchar(max) where there are char(13)s breaking the lines. ( going into a remote view )
>>
>>
>>I can figure the first left() statement using charindex(char(13),mfield,1) but I'm struggling with the substring() syntax for lines 2 -> n
>>
>>If I am converting an app where I'm doing a lot of this should I have a UDF that would extract row(n) from a varchar(max) ?
>>
>>Guidance appreciated, as always.
>
>(I don't what happened to while I was writing I lost what I wrote here! grrr)
>
>AFAIK you're using C#. If so you could use StringReader.ReadLine to read lines from a string. You can create a C# based UDF that would return you given line(s) or all the lines as a table and register that assembly in SQL2005. It works nicely and faster than T-SQL ways. One drawback, if you need to use that database in a hosting location, not all hosts allow CLR to be enabled in SQL2005.
>Instead of CLR getting the field content to a string and doing StringReader.ReadLine at client side might also be a more effective solution.
>Cetin
Charles Hankey
Though a good deal is too strange to be believed, nothing is too strange to have happened.
- Thomas Hardy
Half the harm that is done in this world is due to people who want to feel important. They don't mean to do harm-- but the harm does not interest them. Or they do not see it, or they justify it because they are absorbed in the endless struggle to think well of themselves.
-- T. S. Eliot
Democracy is two wolves and a sheep voting on what to have for lunch.
Liberty is a well-armed sheep contesting the vote.
- Ben Franklin
Pardon him, Theodotus. He is a barbarian, and thinks that the customs of his tribe and island are the laws of nature.