Imports Microsoft.SqlServer.Server
Imports System.Data.SqlTypes

Public Class CLRSplit
	Implements IEnumerator

	...

End Class

Public Class SplitString

	<Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName:="FillRow", TableDefinition:="output IdVal int")> _
	Public Shared Function DoSplit(instr As SqlChars, <SqlFacet(IsFixedLength:=True, MaxSize:=1)> delimiter As SqlString) As IEnumerator
		Return (If((instr.IsNull OrElse delimiter.IsNull), New CLRSplit("", ","c), New CLRSplit(instr.ToSqlString().Value, Convert.ToChar(delimiter.Value))))
	End Function

	Public Shared Sub FillRow(obj As Object, ByRef output As SqlString)
		output = DirectCast(obj, SqlInt32)
	End Sub

End Class

sp_configure 'clr enabled', 1 
GO 
RECONFIGURE 
GO
Success

CREATE ASSEMBLY mySplitter
FROM 'D:\mySplitter.dll'
WITH PERMISSION_SET = SAFE
GO
Success

CREATE FUNCTION dbo.myfn_ClrSplitter
	(
	@Ids nvarchar(max),
	@separator char(1)
	)
RETURNS TABLE
	(
	IdVal int NOT NULL
	)
AS EXTERNAL NAME mySplitter.DoSplit
Msg 102, Level 15, State 1, Procedure myfn_ClrSplitter, Line 11
Incorrect syntax near 'DoSplit'.

AS EXTERNAL NAME mySplitter.SplitString.DoSplit

quote:

---

In Visual Studio, if you use object browser to look at the mysplitter.dll, does it show the class and the method with the correct visibility?

---

• Public Shared Sub FillRow(obj As Object, ByRef output As System.Data.SqlTypes.SqlString)
  Member of mySplitter.SplitString
• Public Shared Function DoSplit(instr As System.Data.SqlTypes.SqlChars, delimiter As System.Data.SqlTypes.SqlString) As System.Collections.IEnumerator
  Member of mySplitter.SplitString

quote:

---

Is there any reason you can't use Adam's code verbatim?

---

CREATE FUNCTION SqlDoubleVB (@i INT )
RETURNS INT
AS EXTERNAL NAME TestVBFunctionAssembly.[ProjectVB.ClassVB].DoubleVB

Public Class ClassVB
    <Microsoft.SqlServer.Server.SqlFunction()>
    Public Shared Function DoubleVB(i As SqlTypes.SqlInt32) As SqlTypes.SqlInt32
        Return 2 * i
    End Function
End Class

Public Shared Sub FillRow(obj As Object, <Out()> ByRef IdVal As SqlString)
	IdVal = DirectCast(obj, SqlString)
End Sub

Msg 6552, Level 16, State 3, Procedure e4fn_ClrSplitter, Line 1
CREATE FUNCTION for "myfn_ClrSplitter" failed because T-SQL and CLR types for parameter "@delimiter" do not match.

Imports System.Data
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlTypes
Imports System.Data.SqlClient
Imports System.Collections
Imports System.Runtime.InteropServices

Public Class SplitString

	Private Class CLRSplit
		Implements IEnumerator

		Private lastPos As Integer
		Private nextPos As Integer
		Private theString As String
		Private delimiter As Char

		Public Sub New(s As String, d As Char)
			Me.theString = s
			Me.delimiter = d
			Me.lastPos = -1
			Me.nextPos = -1
		End Sub

		Public ReadOnly Property Current() As Object Implements IEnumerator.Current
			Get
				Return theString.Substring(lastPos, nextPos - lastPos).Trim()
			End Get
		End Property

		Public Function MoveNext() As Boolean Implements IEnumerator.MoveNext
			If nextPos >= theString.Length Then
				Return False
			Else
				lastPos = nextPos + 1
				If lastPos = theString.Length Then
					Return False
				End If
				nextPos = theString.IndexOf(delimiter, lastPos)
				If nextPos = -1 Then
					nextPos = theString.Length
				End If
				Return True
			End If
		End Function

		Public Sub Reset() Implements IEnumerator.Reset
			Me.lastPos = -1
			Me.nextPos = -1
		End Sub

	End Class

	<Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName:="FillRow", TableDefinition:="IdVal INT")> _
	Public Shared Function DoSplit(instr As SqlString, <SqlFacet(IsFixedLength:=True, MaxSize:=1)> delimiter As SqlChars) As IEnumerable
		Return (If((instr.IsNull OrElse delimiter.IsNull), New CLRSplit("", ","c), New CLRSplit(instr.Value, Convert.ToChar(delimiter.Value))))
	End Function

	Public Shared Sub FillRow(obj As Object, <Out()> ByRef IdVal As SqlInt32)
		IdVal = DirectCast(obj, SqlInt32)
	End Sub

End Class

    <Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName:="FillRow", TableDefinition:="output INT")> _
    Public Shared Function DoSplit(instr As SqlString, <SqlFacet(IsFixedLength:=True, MaxSize:=1)> delimiter As SqlChars) As IEnumerator
        Return (If((instr.IsNull OrElse delimiter.IsNull), New CLRSplit("", ","c), New CLRSplit(instr.Value, Convert.ToChar(delimiter.Value))))
    End Function

    Public Shared Sub FillRow(obj As Object, ByRef IdVal As SqlInt32)
        IdVal = Convert.ToInt32(obj)
    End Sub

Public Shared Sub FillRow(obj As Object, ByRef output As SqlString)
	output = DirectCast(obj, SqlInt32)
	output = Convert.ToInt32(obj)
End Sub

IdVal = New SqlInt32(obj)

Dim intVal As Integer
intVal = obj
IdVal = intVal

quote:

---

IdVal = New SqlInt32(obj)

---

<Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName:="FillRow", TableDefinition:="output INT")> _
Public Shared Function SplitToIntegers(instr As SqlString, <SqlFacet(IsFixedLength:=True, MaxSize:=1)> delimiter As SqlChars) As IEnumerator

	
	' Throw New Exception(delimiter.Length.ToString) ' always returns '1' regardless of char
	If delimiter.Length <> 1 Then
		Throw New ArgumentException("Length: " & delimiter.Length.ToString & " - The delimiter character must be a single character")
		Exit Function
	End If
	Return New CLRSplit(instr.Value, Convert.ToChar(delimiter.Value))
End Function

If delimiter.Value.ToString.Trim = String.Empty Then
	Throw New ArgumentException...

CREATE FUNCTION [dbo].[fnSplitStringList] (@StringList VARCHAR(MAX))
RETURNS @TableList TABLE( StringLiteral VARCHAR(128))
BEGIN
    DECLARE @StartPointer INT, @EndPointer INT
    SELECT @StartPointer = 1, @EndPointer = CHARINDEX(',', @StringList)
    WHILE (@StartPointer < LEN(@StringList) + 1) 
    BEGIN
        IF @EndPointer = 0 
            SET @EndPointer = LEN(@StringList) + 1
        INSERT INTO @TableList (StringLiteral) 
        VALUES(LTRIM(RTRIM(SUBSTRING(@StringList, @StartPointer, 
                                     @EndPointer - @StartPointer))))
        SET @StartPointer = @EndPointer + 1
        SET @EndPointer = CHARINDEX(',', @StringList, @StartPointer)
    END -- WHILE
    RETURN
END -- FUNCTION

SELECT * FROM [dbo].[fnSplitStringList]('Andrew,Brian,Charles')

quote:

---

Forgive me for getting back to basics here but why on earth are you using a CLR function and VB.bnet to do something as simple as split a comma separated list into a table?
...

---

quote:

---

Strangely, even when a blank delimiter is passed, the exception I added never gets thrown, and FillRow() executes. So if a blank delimiter is passed to the function, the error I actually witness is:

---

CREATE FUNCTION dbo.myfn_ClrSplitter
	(
	@Ids nvarchar(max),
	@separator char(1) -- Change this to nvarchar(25) to see the exception.
	)

quote:

---

Originally posted by R

Try this SQL code below and save yourself a world of pain.

Waiting for SQL to split a 10,000 comma list is pain! The CLR function does it in a few hundredths of a second. Here is the proof that CLR beats SQL hands down... http://sqlblog.com/blogs/aaron_bertrand/archive/2010/07/07/splitting-a-list-of-integers-another-roundup.aspx

---

quote:

---

Originally posted by theboyholty

quote:

---

Originally posted by R

Try this SQL code below and save yourself a world of pain.

Waiting for SQL to split a 10,000 comma list is pain! The CLR function does it in a few hundredths of a second. Here is the proof that CLR beats SQL hands down... http://sqlblog.com/blogs/aaron_bertrand/archive/2010/07/07/splitting-a-list-of-integers-another-roundup.aspx

---

That's a fascinating article. If awards were handed out for services to the SQL community, Aaron would get one for a study like that. We live and learn.

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk A Bury FC supporters website and forum

---