clr sql server function

[xke]

This is my function:

<Microsoft.SqlServer.Server.SqlFunction()_
Public Shared Function RegExMatch(ByVal pattern As String, _
ByVal matchString As String) As Boolean

Dim r1 As Regex = New Regex(pattern.TrimEnd(Nothing),
RegexOptions.Compiled)
Return r1.Match(matchString).Success
End Function
I have noticed that the call:

select dbo.RegexMatch('some_reg_exp_pattern', my_match_content)

will trim my_match_content to 4000 chars.
It means everything over 4000 chars will be ignored. I guess this is
happening because of mapping string to a varchar - which may have a
max 4000 chars.

And my_match_content is actually a column varchar(max).

How can I adapt the function to support biger size strings?
Thanks,
xke

[xke]

Actually I test this function under Test Scripts

<Microsoft.SqlServer.Server.SqlFunction()_
Public Shared Function TestF(ByVal simple_string As String) As
Boolean
dim strTest as string
strTest = "123"
Return true
End Function

The test is based on select dbo.TestF(N'some text')

I put a breakpoint at strTest = "123", anyway 'some text' comes
nvachar with a 4000 maximum.
If I put a longer text it will get into my function trimmed to 4000.

Do you know any workaround?
Thanks,
xke
On Mar 14, 2:47 pm, "xke" <[e-mail]:

This is my function:

<Microsoft.SqlServer.Server.SqlFunction()_
Public Shared Function RegExMatch(ByVal pattern As String, _
ByVal matchString As String) As Boolean

Dim r1 As Regex = New Regex(pattern.TrimEnd(Nothing),
RegexOptions.Compiled)
Return r1.Match(matchString).Success
End Function

I have noticed that the call:

select dbo.RegexMatch('some_reg_exp_pattern', my_match_content)

will trim my_match_content to 4000 chars.
It means everything over 4000 chars will be ignored. I guess this is
happening because of mapping string to a varchar - which may have a
max 4000 chars.

And my_match_content is actually a column varchar(max).

How can I adapt the function to support biger size strings?
Thanks,
xke

[xke]

And the solution is .....

<Microsoft.SqlServer.Server.SqlFunction()_
Public Shared Function RegExMatch(ByVal pattern As String, _
ByVal matchString As SqlChars) As Boolean

Dim r1 As Regex = New Regex(pattern.TrimEnd(Nothing),
RegexOptions.Compiled)
Return r1.Match(New String(matchString.Value)).Success
End Function

Found out from a great article written by David Banister
http://msdn.microsoft.com/msdnmag/is...x/default.aspx

that sqlChars is varchar(max)