SQL Server中的正则表达式，匹配除了字母之外的任何内容

I'm trying to write some regex in SQL Server to match anything before or after a string except a letter.

我正在尝试在SQL Server中编写一些正则表达式来匹配除字母之外的字符串之前或之后的任何内容。

'%ABC%' doesn't work as it includes letters.

'%ABC%'不起作用,因为它包含字母。

'%[^a-z]ABC[^a-z]%' doesn't work because it wont match with any query where the result starts or ends with ABC. Because [^a-z] means any character that is not a letter character, but a character nonetheless.

'%[^ a-z] ABC [^ a-z]%'不起作用,因为它不会与结果以ABC开始或结束的任何查询匹配。因为[^ a-z]表示任何不是字母字符的字符,但仍然是字符。

The regex to solve this would be:

解决这个问题的正则表达式是:

[^a-z]ABC[^a-z]|ABC[^a-z]|[^a-z]ABC

but you cant write this in SQL as:

但你不能在SQL中写这个:

'%[^a-z]ABC[^a-z]|ABC[^a-z]|[^a-z]ABC%'

or

'%[^a-z]ABC[^a-z]%|%ABC[^a-z]%|%[^a-z]ABC%'

I don't want to use OR in my SQL, as I have a stored procedure that includes:

我不想在我的SQL中使用OR,因为我有一个包含以下内容的存储过程:

WHERE var like @var 

and I'd be executing it with something like

我会用类似的东西来执行它

@var = '%[^a-z]APU[^a-z]%'

and I'd rather not have multiple parameters or try and pass multiple values to a single parameter.

而且我宁愿没有多个参数,也不尝试将多个值传递给单个参数。

Is there a way to tweak the regex of '%[^a-z]ABC[^a-z]%' Such that ABC can start or end the string?

有没有办法调整'%[^ a-z] ABC [^ a-z]%'的正则表达式,这样ABC可以开始或结束字符串?

2 个解决方案

#1

You can do this as:

你可以这样做:

where ' ' + var + ' ' like '%[^a-z]ABC[^a-z]%'

That will match patterns at the beginning and end of the string as well.

这也将匹配字符串开头和结尾的模式。

I'm not sure if this really meets your requirement, though.

不过,我不确定这是否真的符合你的要求。

#2

You can also use this CLR code to use real regexes in SQL Server
See after code, the SQL sentence to add into the functions in your SQL Server instance

您还可以使用此CLR代码在SQL Server中使用真正的正则表达式在代码之后,将SQL语句添加到SQL Server实例中的函数中

using System;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
using System.Collections;
using System.Data.SqlTypes;

namespace XXX.DotNet.XXX
{
    /// <summary>
    /// Cette classe est utilisée pour mettre à disposition une librairie de fonctions d'Expressions Régulières destinées pour SQL Server 2005 (et plus) en CLR
    /// </summary>
    public class RegularExpressionFunctions
    {
        /**
        <summary>
        Cette méthode permet de récupérer toutes les sous-chaines d'une chaine correspondant à une expression régulière (sous forme de chaine concaténée)
        </summary>
        <param name="pattern">
        Cette chaîne de caractères représente l'expression régulière à comparer
        </param>
        <param name="sentence">
        Cette chaîne de caractères représente l'expression à évaluer
        </param>
        <param name="separator">
        Cette chaîne de caractères sera insérée entre chaque sous-chaîne
        </param>
        <returns>
        Soit null si aucune sous-chaine ne correspond, soit une chaine correspondant à la concaténation des différentes sous-chaînes séparées par une chaîne de séparation
        </returns>
        */
        [SqlFunction(IsDeterministic = true, IsPrecise = true, Name = "RegExMatches")]
        public static String RegExMatches(String pattern, String sentence, String separator)
        {
            Regex rgx = new Regex(pattern);
            MatchCollection matches = rgx.Matches(sentence);
            int nbFound = matches.Count;
            if(nbFound == 0){return null;}// Retourne null si aucune sous-chaîne ne correspond à l'expression régulière
            String toReturn = "";
            for(int i = 0; i < nbFound; i++)
            {
                Match match = matches[i];
                if(i != 0)
                {
                    toReturn += separator;
                }
                toReturn += match.Value;
            }
            return toReturn;// Retourne les sous-chaînes séparées par la chaîne de séparation
        }

        /**
        <summary>
        Cette méthode permet de récupérer toutes les sous-chaines d'une chaine correspondant à une expression régulière (sous forme de tableau)
        </summary>
        <param name="pattern">
        Cette chaîne de caractères représente l'expression régulière à comparer
        </param>
        <param name="sentence">
        Cette chaîne de caractères représente l'expression à évaluer
        </param>
        <returns>
        Un tableau de taille égale au nombre de sous-chaîne trouvées, contenant dans chaque case une sous-chaîne correspondant à l'expression régulière
        </returns>
        */
        [SqlFunction(Name = "RegExMatchesSplit", FillRowMethodName = "NextSplitRow", DataAccess = DataAccessKind.Read)]
        public static IEnumerable RegExMatchesSplit(String pattern, String sentence)
        {
            Regex rgx = new Regex(pattern);
            MatchCollection matches = rgx.Matches(sentence);
            int nbFound = matches.Count;
            //String[][] toReturn = new String[nbFound][];
            String[] toReturn = new String[nbFound];
            for(int i = 0; i < nbFound; i++)
            {
                /*toReturn[i] = new String[2];
                toReturn[i][0] = sentence;
                toReturn[i][1] = matches[i].Value;*/
                toReturn[i] = matches[i].Value;
            }
            return toReturn;// Retourne les sous-chaînes dans un tableau
        }
        public static void NextSplitRow(Object obj, /*out SqlString sentence, */out SqlString match)
        {
            /*String[] row = (String[])obj;
            sentence = new SqlString(row[0]);
            match = new SqlString(row[1]);*/
            match = new SqlString(obj.ToString());
        }

        /**
        <summary>
        Cette méthode permet de récupérer le nombre de sous-chaînes d'une chaîne correspondant à une expression régulière
        </summary>
        <param name="pattern">
        Cette chaîne de caractères représente l'expression régulière à comparer
        </param>
        <param name="sentence">
        Cette chaîne de caractères représente l'expression à évaluer
        </param>
        <returns>
        Le nombre d'occurences des sous-chaînes trouvée par l'expression régulière dans la chaîne d'entrée
        </returns>
        */
        [SqlFunction(IsDeterministic = true, IsPrecise = true, Name = "RegExNbMatches")]
        public static int RegExNbMatches(String pattern, String sentence)
        {
            return new Regex(pattern).Matches(sentence).Count;// Retourne le nombre de sous-chaînes trouvées
        }

        /**
        <summary>
        Cette méthode permet de savoir si une chaîne de caractère correspond à un pattern d'expression régulière
        </summary>
        <param name="pattern">
        Cette chaîne de caractères représente l'expression régulière à comparer
        </param>
        <param name="sentence">
        Cette chaîne de caractères représente l'expression à évaluer
        </param>
        <returns>
        True si l'expression correspond bien au pattern, false dans le cas contraire
        </returns>
        */
        [SqlFunction(IsDeterministic = true, IsPrecise = true, Name = "RegExIsMatch")]
        public static bool RegExIsMatch(String pattern, String sentence)
        {
            return new Regex(pattern).IsMatch(sentence);
        }

        /**
        TODO - Documentation
        */
        [SqlFunction(IsDeterministic = true, IsPrecise = true, Name = "RegExMatch")]
        public static String RegExMatch(String pattern, String sentence)
        {
            Match match = new Regex(pattern).Match(sentence);
            if(!match.Success){return null;}
            return match.Value;
        }
    }
}

Compile with Framework 3.5 the following command can help

使用Framework 3.5编译以下命令可以提供帮助

"C:\Windows\Microsoft.NET\Framework\v3.5\csc.exe" /t:library RegularExpressionFunctions.cs

Once compiled, put the DLL into C:\CLR\Regex\
(or elsewhere and change the following SQL Query according)

编译完成后,将DLL放入C:\ CLR \ Regex \(或其他地方并更改以下SQL查询)

Then add the functions with the following code and test with the Query that comes next

然后使用以下代码添加函数,并使用下一个查询进行测试

sp_configure 'clr enabled', 1
RECONFIGURE WITH OVERRIDE
-- Drop des fonctions pré-existantes
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.RegExMatches')) DROP FUNCTION dbo.RegExMatches
GO
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.RegExNbMatches')) DROP FUNCTION dbo.RegExNbMatches
GO
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.RegExMatchesSplit')) DROP FUNCTION dbo.RegExMatchesSplit
GO
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.objects WHERE object_id = OBJECT_ID(N'dbo.RegExMatch')) DROP FUNCTION dbo.RegExMatch
GO
-- Drop de l'assembly pré-existante puis recréation de celle-ci
IF EXISTS ( SELECT 1 FROM sys.assemblies asms WHERE asms.name = N'RegExFunction' ) DROP ASSEMBLY [RegExFunction]
CREATE ASSEMBLY RegExFunction FROM 'C:\CLR\Regex\RegularExpressionFunctions.dll' WITH PERMISSION_SET = SAFE
GO
-- Création des fonctions
CREATE FUNCTION dbo.RegExMatches(@pattern NVARCHAR(MAX), @sentence NVARCHAR(MAX), @separator NVARCHAR(MAX)) RETURNS NVARCHAR(MAX)
AS EXTERNAL NAME RegExFunction.[XXX.DotNet.XXX.RegularExpressionFunctions].RegExMatches
GO
CREATE FUNCTION dbo.RegExNbMatches(@pattern NVARCHAR(MAX), @sentence NVARCHAR(MAX)) RETURNS INT
AS EXTERNAL NAME RegExFunction.[XXX.DotNet.XXX.RegularExpressionFunctions].RegExNbMatches
GO
--CREATE FUNCTION dbo.RegExMatchesSplit(@pattern NVARCHAR(MAX), @sentence NVARCHAR(MAX)) RETURNS TABLE (Sujet NVARCHAR(MAX), Match NVARCHAR(MAX))
CREATE FUNCTION dbo.RegExMatchesSplit(@pattern NVARCHAR(MAX), @sentence NVARCHAR(MAX)) RETURNS TABLE (Match NVARCHAR(MAX))
AS EXTERNAL NAME RegExFunction.[XXX.DotNet.XXX.RegularExpressionFunctions].RegExMatchesSplit
GO
CREATE FUNCTION dbo.RegExIsMatch(@pattern NVARCHAR(MAX), @sentence NVARCHAR(MAX)) RETURNS BIT
AS EXTERNAL NAME RegExFunction.[XXX.DotNet.XXX.RegularExpressionFunctions].RegExIsMatch
GO
CREATE FUNCTION dbo.RegExMatch(@pattern NVARCHAR(MAX), @sentence NVARCHAR(MAX)) RETURNS NVARCHAR(MAX)
AS EXTERNAL NAME RegExFunction.[XXX.DotNet.XXX.RegularExpressionFunctions].RegExMatch
GO

Use the following Query to test your functions

使用以下查询来测试您的功能

DECLARE @sentence NVARCHAR(MAX)
DECLARE @regex NVARCHAR(MAX)
DECLARE @regex2 NVARCHAR(MAX)
DECLARE @separator NVARCHAR(MAX)
SET @sentence = 'ABABCCADSQDJIOAZF JAIPZDJKL MNJKCXNjnaze iodjazpdjadpazdoa zdjio'
SET @regex = '[A-z]{6}\ '
SET @regex2 = '^[^a-z]*ABC[^a-z]*$'
SET @separator = ';'
SELECT @regex as 'Regex', @sentence as 'Sentence', dbo.RegExMatch(@regex2, match) FROM dbo.RegExMatchesSplit(@regex, @sentence);
SELECT @regex as 'Regex', @sentence as 'Sentence', dbo.RegExMatches(@regex2, dbo.RegExMatches(@regex, @sentence, @separator), @separator)
SELECT @regex as 'Regex', @sentence as 'Sentence', dbo.RegExNbMatches(@regex,@sentence)
SELECT @regex as 'Regex', @sentence as 'Sentence', dbo.RegExIsMatch(@regex,@sentence)
SELECT @regex as 'Regex', @sentence as 'Sentence', dbo.RegExMatch(@regex2, dbo.RegExMatch(@regex,@sentence))
GO