clr sql server function

  • Thread starter Thread starter xke
  • Start date Start date
X

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
 
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
 
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/issues/07/02/SQLRegex/default.aspx

that sqlChars is varchar(max)
 
Back
Top