Skip to main content Skip to complementary content

Deploying the regular expression function to the SQL server

About this task

You need now to add the new regular expression function to the created project, and then deploy the function to the SQl server. To do that:

Procedure

  1. In the project list in the Solution Explorer panel, expand the node of the project you created and right-click the Test Scripts node.
  2. From the contextual menu, select Add > New Item....
    The Add New Item dialog box is displayed.
  3. From the Templates list, select Class and then in the Name field, enter a name to the user-defined function you want to add to the project, RegExMatch in this example.
    The added function is listed under the created project node in the Solution Explorer panel to the right.
  4. Click Add to validate your changes and close the dialog box.
  5. In the code space to the left, enter the instructions corresponding to the regular expression function you already added to the created project.
    Below is the code for the regular expression function we use in this example.
    Using System;
Using Microsoft.SqlServer.Server;
Using System.Text.RegularExpressions;
Public partial class RegExBase
{
     [SqlFunction(IsDeterministic = true, IsPrecise = true)]
     Public static int RegExMatch( string matchString , string pattern)
     {
            Regex r1 = new Regex(pattern.TrimEnd(null));
            if (r1.Match(matchString.TrimEnd(null)).Success == true) 
            {
                 return 1 ;
            }
            else
            {
                 return 0 ;
            }
Using
     }
};
  6. Press Ctrl+S to save your changes and then on the menu bar, click Build and in the contextual menu select the corresponding item to build the project you created, Build UDF function in this example.
    The lower pane of the window displays a message to confirm that the "build" operation was successful or not.
  7. On the menu bar, click Build and in the contextual menu select the corresponding item to deploy the project you created, Deploy UDF function in this example.
    The lower pane of the window displays a message to confirm that the "deploy" operation was successful, or not.
    If required:
    1. launch SQL Server and check if the created function exists in the function list,
    2. check if the function works well, for more information, see Testing the created function via the SQL Server editor.

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – please let us know!

Leave your feedback here