Korzystanie z wyrażenia regularnego w programie SQL Server

93

Szukam, jak zamienić / zakodować tekst przy użyciu RegEx na podstawie poniższych ustawień / parametrów RegEx:

RegEx.IgnoreCase = True     
RegEx.Global = True     
RegEx.Pattern = "[^a-z\d\s.]+"

Widziałem kilka przykładów dotyczących RegEx, ale nie wiedziałem, jak zastosować to w ten sam sposób w SQL Server. Wszelkie sugestie byłyby pomocne. Dziękuję Ci.

sql-server  regex  sql-server-2008  tsql 
— Control Freak
źródło
1
Cześć, spójrz na ten artykuł: codeproject.com/Articles/42764/…
— Mohsen
Jest też dobrze TSQL + Windows API rozwiązanie na Robyn Page i Phil Factor , który opiera się na VBScript.RegExp klasy, co ja belieave, dostarczany jest na każdej wersji systemu Windows od Windows 2000.
— Julio Nobre
Jeśli absolutnie pozytywnie trzeba RegEx poprzez TSQL, opcja dla SQL Server 2016 i powyżej jest do usług użycie R .
— Dave Mason

Odpowiedzi:

104

Nie musisz wchodzić w interakcje z kodem zarządzanym, ponieważ możesz użyć LIKE :

CREATE TABLE #Sample(Field varchar(50), Result varchar(50))
GO
INSERT INTO #Sample (Field, Result) VALUES ('ABC123 ', 'Do not match')
INSERT INTO #Sample (Field, Result) VALUES ('ABC123.', 'Do not match')
INSERT INTO #Sample (Field, Result) VALUES ('ABC123&', 'Match')
SELECT * FROM #Sample WHERE Field LIKE '%[^a-z0-9 .]%'
GO
DROP TABLE #Sample

Gdy twoja ekspresja się skończy +, możesz iść'%[^a-z0-9 .][^a-z0-9 .]%'

EDYCJA : wyjaśnienie: SQL Server nie obsługuje wyrażeń regularnych bez kodu zarządzanego. W zależności od sytuacji LIKEoperator może być opcją, ale brakuje mu elastyczności zapewnianej przez wyrażenia regularne.

— Rubens Farias
źródło
8
@MikeYoung, masz rację. Ta odpowiedź niepoprawnie odnosi się do +kwantyfikatora, ponieważ {1,2}powinien przyjąć go jako {1, }. O dziwo, to zadziałało na PO.
— Rubens Farias
2
To nie zadziała na serwerze sql, ponieważ nie obsługuje wyrażeń regularnych.
— VVN
10
@VVN LIKEnie jest wyrażeniem regularnym (jest to bardziej ograniczona składnia dopasowywania wzorców), więc brak obsługi wyrażeń regularnych nie oznacza, że ​​to nie zadziała.
— Charles Duffy
@RubensFarias czy nie byłoby miło zaktualizować odpowiedź w świetle komentarzy @ mike-young?
— Sudhanshu Mishra
8

Nieznacznie zmodyfikowana wersja odpowiedzi Julio.

-- MS SQL using VBScript Regex
-- select dbo.RegexReplace('aa bb cc','($1) ($2) ($3)','([^\s]*)\s*([^\s]*)\s*([^\s]*)')
-- $$ dollar sign, $1 - $9 back references, $& whole match

CREATE FUNCTION [dbo].[RegexReplace]
(   -- these match exactly the parameters of RegExp
    @searchstring varchar(4000),
    @replacestring varchar(4000),
    @pattern varchar(4000)
)
RETURNS varchar(4000)
AS
BEGIN
    declare @objRegexExp int, 
        @objErrorObj int,
        @strErrorMessage varchar(255),
        @res int,
        @result varchar(4000)

    if( @searchstring is null or len(ltrim(rtrim(@searchstring))) = 0) return null
    set @result=''
    exec @res=sp_OACreate 'VBScript.RegExp', @objRegexExp out
    if( @res <> 0) return '..VBScript did not initialize'
    exec @res=sp_OASetProperty @objRegexExp, 'Pattern', @pattern
    if( @res <> 0) return '..Pattern property set failed'
    exec @res=sp_OASetProperty @objRegexExp, 'IgnoreCase', 0
    if( @res <> 0) return '..IgnoreCase option failed'
    exec @res=sp_OAMethod @objRegexExp, 'Replace', @result OUT,
         @searchstring, @replacestring
    if( @res <> 0) return '..Bad search string'
    exec @res=sp_OADestroy @objRegexExp
    return @result
END

Będziesz potrzebować włączonych procedur Ole Automation w SQL:

exec sp_configure 'show advanced options',1; 
go
reconfigure; 
go
sp_configure 'Ole Automation Procedures', 1; 
go
reconfigure; 
go
sp_configure 'show advanced options',0; 
go
reconfigure;
go
— Zachary Scott
źródło
2
BTW, znacznie szybciej jest zniszczyć i odtworzyć obiekt regex niż buforować go i ponownie użyć. Przeprowadziliśmy 10000 porównań ze znacznie wyższymi liczbami, wykorzystując ponownie obiekt.
— Zachary Scott,
8

Będziesz musiał zbudować procedurę CLR, która zapewnia funkcjonalność wyrażeń regularnych, jak w tym artykule ilustruje .

Ich przykładowa funkcja wykorzystuje VB.NET:

Imports System
Imports System.Data.Sql
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlTypes
Imports System.Runtime.InteropServices
Imports System.Text.RegularExpressions
Imports System.Collections 'the IEnumerable interface is here  


Namespace SimpleTalk.Phil.Factor
    Public Class RegularExpressionFunctions
        'RegExIsMatch function
        <SqlFunction(IsDeterministic:=True, IsPrecise:=True)> _
        Public Shared Function RegExIsMatch( _
                                            ByVal pattern As SqlString, _
                                            ByVal input As SqlString, _
                                            ByVal Options As SqlInt32) As SqlBoolean
            If (input.IsNull OrElse pattern.IsNull) Then
                Return SqlBoolean.False
            End If
            Dim RegExOption As New System.Text.RegularExpressions.RegExOptions
            RegExOption = Options
            Return RegEx.IsMatch(input.Value, pattern.Value, RegExOption)
        End Function
    End Class      ' 
End Namespace

... i jest instalowany w SQL Server przy użyciu następującego kodu SQL (zastępując zmienne rozdzielane „%” ich rzeczywistymi odpowiednikami:

sp_configure 'clr enabled', 1
RECONFIGURE WITH OVERRIDE

IF EXISTS ( SELECT   1
            FROM     sys.objects
            WHERE    object_id = OBJECT_ID(N'dbo.RegExIsMatch') ) 
   DROP FUNCTION dbo.RegExIsMatch
go

IF EXISTS ( SELECT   1
            FROM     sys.assemblies asms
            WHERE    asms.name = N'RegExFunction ' ) 
   DROP ASSEMBLY [RegExFunction]

CREATE ASSEMBLY RegExFunction 
           FROM '%FILE%'
GO

CREATE FUNCTION RegExIsMatch
   (
    @Pattern NVARCHAR(4000),
    @Input NVARCHAR(MAX),
    @Options int
   )
RETURNS BIT
AS EXTERNAL NAME 
   RegExFunction.[SimpleTalk.Phil.Factor.RegularExpressionFunctions].RegExIsMatch
GO

--a few tests
---Is this card a valid credit card?
SELECT dbo.RegExIsMatch ('^(?:4[0-9]{12}(?:[0-9]{3})?|5[1-5][0-9]{14}|6(?:011|5[0-9][0-9])[0-9]{12}|3[47][0-9]{13}|3(?:0[0-5]|[68][0-9])[0-9]{11}|(?:2131|1800|35\d{3})\d{11})$','4241825283987487',1)
--is there a number in this string
SELECT dbo.RegExIsMatch( '\d','there is 1 thing I hate',1)
--Verifies number Returns 1
DECLARE @pattern VARCHAR(255)
SELECT @pattern ='[a-zA-Z0-9]\d{2}[a-zA-Z0-9](-\d{3}){2}[A-Za-z0-9]'
SELECT  dbo.RegExIsMatch (@pattern, '1298-673-4192',1),
        dbo.RegExIsMatch (@pattern,'A08Z-931-468A',1),
        dbo.RegExIsMatch (@pattern,'[A90-123-129X',1),
        dbo.RegExIsMatch (@pattern,'12345-KKA-1230',1),
        dbo.RegExIsMatch (@pattern,'0919-2893-1256',1)
— mwigdahl
źródło
To jest w klasycznym ASP, czy obsługuje? Myślę, że CLR jest tylko dla funkcji .NET, prawda?
— Control Freak
4
Procedury CLR są instalowane w środowisku SQL Server i mogą być wywoływane jak każda inna procedura składowana lub funkcja zdefiniowana przez użytkownika, więc jeśli klasyczna ASP może wywołać procedurę składowaną lub funkcję zdefiniowaną przez użytkownika, może wywołać procedurę CLR.
— mwigdahl
1
Chociaż ten link może odpowiedzieć na pytanie, lepiej jest zawrzeć tutaj zasadnicze części odpowiedzi i podać link do odniesienia. Odpowiedzi zawierające tylko łącze mogą stać się nieprawidłowe, jeśli połączona strona ulegnie zmianie. - Z recenzji
— Federico klez Culloca
Dzięki @FedericoklezCulloca. To była stara odpowiedź i odpowiednio ją zaktualizowałem.
— mwigdahl
@mwigdahl dzięki za to. Widzę, że jest stary, ale wyskoczył w kolejce recenzji :)
— Federico klez Culloca
7

Wyrażenia regularne w implementacji baz danych SQL Server Zastosowanie

Wyrażenie regularne - opis
. Dopasuj dowolny znak
* Dopasuj dowolny znak
+ Dopasuj co najmniej jedno wystąpienie wyrażenia przed
^ Rozpocznij na początku wiersza
$ Wyszukaj na końcu wiersza
< Dopasuj tylko, jeśli słowo zaczyna się w tym miejscu
> Dopasuj tylko, jeśli słowo kończy się w tym miejscu
\ n Dopasuj znak końca wiersza
[] Dopasuj dowolny znak w nawiasach
[^ ...] Dopasowuje dowolny znak niewymieniony po ^
[ABQ]% Ciąg musi zaczynać się od liter A, B lub Q i może mieć dowolną długość
[AB] [CD]% Ciąg musi mieć długość dwa lub więcej i musi zaczynać się od A lub B i mieć C lub D jako drugi znak
[AZ]% Ciąg może mieć dowolną długość i musi zaczynać się dowolną literą od A do Z
[A -Z0-9]% Łańcuch może mieć dowolną długość i musi zaczynać się dowolną literą od A do Z lub cyfrą od 0 do 9
[^ AC]% Łańcuch może mieć dowolną długość, ale nie może zaczynać się od liter od A do C
% [AZ] Ciąg może mieć dowolną długość i musi kończyć się dowolną literą od A do Z
% [% $ # @]% Ciąg może mieć dowolną długość i musi zawierać co najmniej jeden ze znaków specjalnych zawartych w wspornik

— Ravi Makwana
źródło
1

Podobne podejście do odpowiedzi @ mwigdahl, możesz również zaimplementować środowisko CLR .NET w C #, z kodem takim jak;

using System.Data.SqlTypes;
using RX = System.Text.RegularExpressions;

public partial class UserDefinedFunctions
{
 [Microsoft.SqlServer.Server.SqlFunction]
 public static SqlString Regex(string input, string regex)
 {
  var match = RX.Regex.Match(input, regex).Groups[1].Value;
  return new SqlString (match);
 }
}

Instrukcje instalacji można znaleźć tutaj

— Fiach Reid
źródło
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.