T-SQL의 SQL Server 정규식

T-SQL의 SQL Server 정규식

---

SQL Server 용으로 작성된 정규식 라이브러리가 있습니까 T-SQL(아니요 CLR, 확장되지 않고 SP순수 T-SQL합니까)?

(공유 호스팅과 함께 작동해야 함)

편집하다:

• 내가 아는 덕분에 대해 PATINDEX, LIKE,XP_는 SPS andCLR` 솔루션을

• 나는 또한 그것이 최고의 장소가 아니라는 것을 알고 있습니다 regex. 질문은 이론적입니다 :)

• 기능 감소도 허용됩니다

---

방법에 대한 PATINDEX의 기능?

TSQL의 패턴 일치는 완전한 정규식 라이브러리는 아니지만 기본 사항을 제공합니다.

(온라인 서적에서)

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]).

---

CLR과 함께 정규식을 사용하는 데 관심이 있다면 여기에 해결책이 있습니다. 아래 함수 (C # .net 4.5)는 패턴이 일치하면 1을, 패턴이 일치하지 않으면 0을 반환합니다. 하위 쿼리에서 행에 태그를 지정하는 데 사용합니다. SQLfunction 속성은이 방법이 SQL 서버가 사용할 실제 UDF임을 SQL 서버에 알려줍니다. 파일을 management studio에서 액세스 할 수있는 장소에 dll로 저장하십시오.

// 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;
        }
    }
}

Management Studio에서 프로그래밍 기능-어셈블리-새 어셈블리를 통해 dll 파일 가져 오기

그런 다음이 쿼리를 실행하십시오.

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

그런 다음 어셈블리를 저장 한 데이터베이스를 통해 기능에 완전히 액세스 할 수 있어야합니다.

그런 다음 쿼리에서 사용하십시오.

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

---

There is some basic pattern matching available through using LIKE, where % matches any number and combination of characters, _ matches any one character, and [abc] could match a, b, or c... There is more info on the MSDN site.

---

In case anyone else is still looking at this question, http://www.sqlsharp.com/ is a free, easy way to add regular expression CLR functions into your database.

---

If you are using SQL Server 2016 or above, you can use sp_execute_external_script along with R. It has functions for Regular Expression searches, such as grep and grepl.

Here's an example for email addresses. I'll query some "people" via the SQL Server database engine, pass the data for those people to R, let R decide which people have invalid email addresses, and have R pass back that subset of people to SQL Server. The "people" are from the [Application].[People] table in the [WideWorldImporters] sample database. They get passed to the R engine as a dataframe named InputDataSet. R uses the grepl function with the "not" operator (exclamation point!) to find which people have email addresses that don't match the RegEx string search pattern.

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)))

Note that the appropriate features must be installed on the SQL Server host. For SQL Server 2016, it is called "SQL Server R Services". For SQL Server 2017, it was renamed to "SQL Server Machine Learning Services".

Closing Thoughts Microsoft's implementation of SQL (T-SQL) doesn't have native support for RegEx. This proposed solution may not be any more desirable to the OP than the use of a CLR stored procedure. But it does offer an additional way to approach the problem.

---

You can use VBScript regular expression features using OLE Automation. This is way better than the overhead of creating and maintaining an assembly. Please make sure you go through the comments section to get a better modified version of the main one.

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;

If you get SQL Server blocked access to procedure 'sys.sp_OACreate'... error, use sp_reconfigure to enable Ole Automation Procedures. (Yes, unfortunately that is a server level change!)

More information about the Test method is available here

Happy coding

참고URL : https://stackoverflow.com/questions/194652/sql-server-regular-expressions-in-t-sql