Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

Page History: Regular Expressions in SQL Server

Compare Page Revisions



« Older Revision - Back to Page History - Newer Revision »


Page Revision: Thu, Apr 08, 2010, 9:13 AM


Overview

The dbo.RegExSearch function will allow you to use regular expressions within T-SQL code.

Sample Usage

The following SQL will extract the ZIP code from the specified sentence.

select ZipCode = dbo.RegExSearch('My ZIP code is 30303, which is in Atlanta, Georgia.','(?<zip>[0-9]{5})','zip')

Source Code

VB.NET

{copytext|SourceVb}
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Text.RegularExpressions

Partial Public Class UserDefinedFunctions
    <Microsoft.SqlServer.Server.SqlFunction()> _
    Public Shared Function RegExSearch(ByVal searchIn As SqlString, ByVal pattern As SqlString, _
    ByVal groupName As SqlString) As SqlString

        Dim result As String = ""
        Dim regex As Regex = New Regex(pattern.Value)
        Dim mc As MatchCollection = regex.Matches(searchIn.Value)

        If mc IsNot Nothing _
        AndAlso mc.Count > 0 _
        AndAlso mc(0).Groups.Count > 0 _
        AndAlso mc(0).Groups(groupName.Value) IsNot Nothing Then

            result = mc(0).Groups(groupName.Value).Value

        End If

        Return New SqlString(result)

    End Function
End Class

C#

{copytext|SourceCs}
TODO

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.