SQL Server Regular expressions in T-SQL

Question

Is there any regular expression library written in T-SQL  no CLR  no extended SP  pure T-SQL  for SQL Server  and that should work with shared hosting   Edit    Thanks  I know about PATINDEX  LIKE  xp  sps and CLR solutions I also know it is not the best place for regex  the question is theoretical    Reduced functionality is also accepted

User · Answer

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

User · Answer

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

User · Answer

How about the PATINDEX function   The pattern matching in TSQL is not a complete regex library  but it gives you the basics    From 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

User · Answer

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

User · Answer

How about the PATINDEX function   The pattern matching in TSQL is not a complete regex library  but it gives you the basics    From 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

User · Answer

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      lt your regex pattern goes here gt    DECLARE  matchstring varchar 8000      lt string to search goes here gt    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

User · Answer

If anybody is interested in using regex with CLR here is a solution  The function below  C   net 4 5  returns a 1 if the pattern is matched and a 0 if the pattern is not matched  I use it to tag lines in sub queries  The SQLfunction attribute tells sql server that this method is the actual UDF that SQL server will use  Save the file as a dll in a place where you can access it from 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  gt  0                                        returnVal   1                                              catch                               returnVal   0                             return returnVal                      In management studio import the dll file via programability -- assemblies -- new assembly  Then run this query    CREATE FUNCTION RegexContain  text NVARCHAR 50    pattern NVARCHAR 50   RETURNS smallint  AS EXTERNAL NAME CLR Functions  CLR Functions myFunctions  RegexContain   Then you should have complete access to the function via the database you stored the assembly in    Then use in queries like so    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

User · Answer

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      lt your regex pattern goes here gt    DECLARE  matchstring varchar 8000      lt string to search goes here gt    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

User · Answer

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  lt - InputDataSet  OutputDataSet  lt - 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

User · Answer

How about the PATINDEX function   The pattern matching in TSQL is not a complete regex library  but it gives you the basics    From 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

User · Answer

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

User · Answer

If anybody is interested in using regex with CLR here is a solution  The function below  C   net 4 5  returns a 1 if the pattern is matched and a 0 if the pattern is not matched  I use it to tag lines in sub queries  The SQLfunction attribute tells sql server that this method is the actual UDF that SQL server will use  Save the file as a dll in a place where you can access it from 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  gt  0                                        returnVal   1                                              catch                               returnVal   0                             return returnVal                      In management studio import the dll file via programability -- assemblies -- new assembly  Then run this query    CREATE FUNCTION RegexContain  text NVARCHAR 50    pattern NVARCHAR 50   RETURNS smallint  AS EXTERNAL NAME CLR Functions  CLR Functions myFunctions  RegexContain   Then you should have complete access to the function via the database you stored the assembly in    Then use in queries like so    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

User · Answer

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

User · Answer

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  lt - InputDataSet  OutputDataSet  lt - 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

User · Answer

How about the PATINDEX function   The pattern matching in TSQL is not a complete regex library  but it gives you the basics    From 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

User · Answer

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

[sql-server] SQL Server Regular expressions in T-SQL

Examples related to sql-server

Examples related to regex

Examples related to tsql