Jasinski Technical Wiki


Home Page
All Pages

Quick Search
Advanced Search »

Contributor Links

Create a new Page
File Management
Your Profile

Other Wiki Sections



Regular Expressions in SQL Server

Modified on Mon, Feb 13, 2012, 12:50 PM by Administrator Categorized as Regular Expressions, SQL Server


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.'

Source Code


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





I had a set of data in a database field that was of the format "description.... $xxx description2... $yyy ...". Each description was followed by a dollar amount, and we could have any number of these description-amount pairs in the text. The dollar amount always started with a dollar sign, and I wanted to exclude certain amounts based on the description.

Source Code

<Microsoft.SqlServer.Server.SqlFunction()> _
Public Shared Function ParseMyText(ByVal input As SqlString) As SqlDouble

    Dim inputValue As String = input.Value
    Dim pattern As String = "(?<descrip>.+?)[$](?<value>[0-9]{1,10}(.[0-9]{1,10}){0,1})"
    Dim regex As Regex = New Regex(pattern)
    Dim mc As MatchCollection = regex.Matches(inputValue)
    Dim result As Double = 0

    If mc IsNot Nothing AndAlso mc.Count > 0 Then

        For Each m As Match In mc

            Dim desc As String = m.Groups("descrip").Value.Trim()

            If Not desc.Contains("some text") Then

                Dim v As String = m.Groups("value").Value
                Dim d As Double = 0
                If Double.TryParse(v, d) Then
                    result += d
                End If

            End If


    End If

    Return New SqlDouble(result)

End Function

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