SQL Server Wyrażenia regularne w T-SQL

Czy istnieje biblioteka wyrażeń regularnych napisana w języku T-SQL (bez środowiska CLR, bez rozszerzonego SP, czystego języka T-SQL) dla programu SQL Server, która powinna działać w przypadku hostingu współdzielonego?

Edytować:

• Dzięki, wiem o PATINDEX, LIKE, xp_ spsoraz rozwiązania CLR
• Wiem też, że to nie jest najlepsze miejsce na regex, pytanie jest teoretyczne :)
• Dopuszczalna jest również ograniczona funkcjonalność

A co z funkcją PATINDEX ?

Dopasowywanie wzorców w TSQL nie jest pełną biblioteką wyrażeń regularnych, ale zapewnia podstawy.

(Z Books Online)

Wildcard  Meaning  
% Any string of zero or more characters.

_ Any single character.

[ ] Any single character within the specified range 
    (for example, [a-f]) or set (for example, [abcdef]).

[^] Any single character not within the specified range 
    (for example, [^a - f]) or set (for example, [^abcdef]).

Jeśli ktoś jest zainteresowany używaniem wyrażenia regularnego z CLR, oto rozwiązanie. Funkcja poniżej (C # .net 4.5) zwraca 1, jeśli wzorzec jest dopasowany, i 0, jeśli wzorzec nie jest dopasowany. Używam go do oznaczania linii w zapytaniach podrzędnych. Atrybut funkcji SQL informuje serwer sql, że ta metoda jest faktycznym UDF, którego będzie używał serwer SQL. Zapisz plik jako dll w miejscu, do którego masz dostęp z Management Studio.

// default using statements above
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;

namespace CLR_Functions
{   
    public class myFunctions
    {
        [SqlFunction]
        public static SqlInt16 RegexContain(SqlString text, SqlString pattern)
        {            
            SqlInt16 returnVal = 0;
            try
            {
                string myText = text.ToString();
                string myPattern = pattern.ToString();
                MatchCollection mc = Regex.Matches(myText, myPattern);
                if (mc.Count > 0)
                {
                    returnVal = 1;
                }
            }
            catch
            {
                returnVal = 0;
            }

            return returnVal;
        }
    }
}

W studiu zarządzania zaimportuj plik dll przez programowalność - zestawy - nowy zespół

Następnie uruchom to zapytanie:

CREATE FUNCTION RegexContain(@text NVARCHAR(50), @pattern NVARCHAR(50))
RETURNS smallint 
AS
EXTERNAL NAME CLR_Functions.[CLR_Functions.myFunctions].RegexContain

Następnie powinieneś mieć pełny dostęp do funkcji za pośrednictwem bazy danych, w której zapisałeś zestaw.

Następnie użyj w zapytaniach takich jak:

SELECT * 
FROM 
(
    SELECT
        DailyLog.Date,
        DailyLog.Researcher,
        DailyLog.team,
        DailyLog.field,
        DailyLog.EntityID,
        DailyLog.[From],
        DailyLog.[To],
        dbo.RegexContain(Researcher, '[\p{L}\s]+') as 'is null values'
    FROM [DailyOps].[dbo].[DailyLog]
) AS a
WHERE a.[is null values] = 0

Istnieje kilka podstawowych dopasowań wzorców dostępnych za pomocą funkcji LIKE, gdzie% pasuje do dowolnej liczby i kombinacji znaków, _ pasuje do dowolnego znaku, a [abc] może dopasować a, b lub c ... Więcej informacji można znaleźć w witrynie MSDN .

Jeśli korzystasz z SQL Server 2016 lub nowszego, możesz używać sp_execute_external_scriptrazem z R. Ma funkcje do wyszukiwania wyrażeń regularnych, takie jak grepi grepl.

Oto przykład adresów e-mail. Zapytam kilka „osób” za pośrednictwem silnika bazy danych SQL Server, przekażę dane tych osób do R, pozwolę R zdecydować, które osoby mają nieprawidłowe adresy e-mail i pozwolę R przekazać z powrotem podzbiór osób do SQL Server. „Osoby” pochodzą z [Application].[People]tabeli w [WideWorldImporters]przykładowej bazie danych. Są przekazywane do silnika języka R jako ramka danych o nazwie InputDataSet. R używa funkcji grepl z operatorem „not” (wykrzyknik!), Aby znaleźć osoby, których adresy e-mail nie pasują do wzorca wyszukiwania ciągu RegEx.

EXEC sp_execute_external_script 
 @language = N'R',
 @script = N' RegexWithR <- InputDataSet;
OutputDataSet <- RegexWithR[!grepl("([_a-z0-9-]+(\\.[_a-z0-9-]+)*@[a-z0-9-]+(\\.[a-z0-9-]+)*(\\.[a-z]{2,4}))", RegexWithR$EmailAddress), ];',
 @input_data_1 = N'SELECT PersonID, FullName, EmailAddress FROM Application.People'
 WITH RESULT SETS (([PersonID] INT, [FullName] NVARCHAR(50), [EmailAddress] NVARCHAR(256)))

Zwróć uwagę, że odpowiednie funkcje muszą być zainstalowane na hoście SQL Server. W przypadku programu SQL Server 2016 nosi nazwę „Usługi SQL Server R”. W przypadku programu SQL Server 2017 jego nazwa została zmieniona na „SQL Server Machine Learning Services”.

Uwagi końcowe Implementacja SQL (T-SQL) firmy Microsoft nie obsługuje natywnej obsługi wyrażeń regularnych. To proponowane rozwiązanie może nie być bardziej pożądane dla OP niż użycie procedury składowanej CLR. Ale oferuje dodatkowy sposób podejścia do problemu.

Na wypadek, gdyby ktokolwiek jeszcze szukał tego pytania, http://www.sqlsharp.com/ to darmowy, łatwy sposób na dodanie funkcji CLR wyrażeń regularnych do bazy danych.

Za pomocą automatyzacji OLE można używać funkcji wyrażeń regularnych VBScript. Jest to o wiele lepsze niż obciążenie związane z tworzeniem i utrzymaniem zespołu. Upewnij się, że przejrzałeś sekcję komentarzy, aby uzyskać lepszą zmodyfikowaną wersję głównej.

http://blogs.msdn.com/b/khen1234/archive/2005/05/11/416392.aspx

DECLARE @obj INT, @res INT, @match BIT;
DECLARE @pattern varchar(255) = '<your regex pattern goes here>';
DECLARE @matchstring varchar(8000) = '<string to search goes here>';
SET @match = 0;

-- Create a VB script component object
EXEC @res = sp_OACreate 'VBScript.RegExp', @obj OUT;

-- Apply/set the pattern to the RegEx object
EXEC @res = sp_OASetProperty @obj, 'Pattern', @pattern;

-- Set any other settings/properties here
EXEC @res = sp_OASetProperty @obj, 'IgnoreCase', 1;

-- Call the method 'Test' to find a match
EXEC @res = sp_OAMethod @obj, 'Test', @match OUT, @matchstring;

-- Don't forget to clean-up
EXEC @res = sp_OADestroy @obj;

Jeśli pojawi się SQL Server blocked access to procedure 'sys.sp_OACreate'...błąd, użyj, sp_reconfigureaby włączyć Ole Automation Procedures. (Tak, niestety jest to zmiana na poziomie serwera!)

Więcej informacji o Testmetodzie znajduje się tutaj

Miłego kodowania