Sorry - just realized I got "paged" to the SSC forum during the long vacation weekend....:)

Yes - you could use a CLR Regex Function to do just that if you want to. It's actually rather cute, since it allows you to "throw out" the result if you don't have BOTH (if you so desire).

I just put this together, if you should want to go down that path.

Here's the CLR (.NET 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

Private Const optionS As RegexOptions = RegexOptions.CultureInvariant

<Microsoft.SqlServer.Server.SqlFunction(IsDeterministic:=True, IsPrecise:=True)< _

Public Shared Function RegexMatch(ByVal input As SqlChars, ByVal pattern As SqlString) As SqlString

' Add your code here

Dim rex As System.Text.RegularExpressions.Regex = New System.Text.RegularExpressions.Regex(pattern.Value, optionS)

Return New SqlString(CType(rex.IsMatch(New String(input.Value)), String))

End Function

<Microsoft.SqlServer.Server.SqlFunction(IsDeterministic:=True, IsPrecise:=True)< _

Public Shared Function RegexMatchGroup(ByVal input As SqlChars, ByVal pattern As SqlString, byval groupnum as SqlInt32) As SqlString

Dim rex As System.Text.RegularExpressions.Regex = New System.Text.RegularExpressions.Regex(pattern.Value, optionS)

Return New SqlString(CType(rex.Matches(New String(input.Value))(0).Groups(groupnum.Value).ToString, String))

End Function

End Class

At that point, you could use something like this to pick out the pieces:

select dbo.regexmatchgroup('80 12345 p9a996458aaa','^(?<1>[0-9]{2}) (?<2>[0-9]{3,5})',2)

The last parameter could be 0, 1, or 2 (0=whole string, 1/2 = the named backreferences to 80 and 12345 respectively in my example).

It does require (at least for me) knowing how to correctly structure the grouping constructs for .NET, but this should give you a running start.