(via Dice News in Tech)Software development is the most in-demand skill for technology jobs in the U.S., according to a study by Wanted Analytics. More than 232,000 jobs for software developers have been advertised online in the past 90 days, an increase of 3 percent over…
This is the third article in my series, mdq.regex CLR Functions. In my first two articles we reviewed mdq.RegexIsMatch and mdq.RegexMatches. In each article I demonstrated how to use these CLR functions to pass regular expressions (regex for short) to your T-SQL queries. Today I will show you how to create a couple tables with check constraints that use regex to evaluate input using mdq.RegexIsMatch.
Setting up mdq.RegexIsMatch is extremely quick and simple provided you have the right credentials. See this article for detailed instructions on how to setup mdq.RegexIsMatch.
Creating check constraints to test for tricky string patterns such as a valid IP Address or E-mail address can be a headache using T-SQL. Luckily, regex is perfect for this sort of task; below are two examples. In the first example we create a table with a column that only accepts valid E-mail addresses, the second only accepts valid IP addresses. This is easiest to learn by example so let's get to it...
Valid E-mail Address
CREATE TABLE Customers
( cust_name varchar(100),
cust_email nvarchar(200)
CHECK (
mdq.RegexIsMatch([Cust_Email],
'^(([a-zA-Z0-9!#\$%\^&\*\{\}''`\+=-_\|/\?]+(\.[a-zA-Z0-9!#\$%\^&\*\{\}''`\+=-_\|/\?]+)*){1,64}@(([A-Za-z0-9]+[A-Za-z0-9-_]*){1,63}\.)*(([A-Za-z0-9]+[A-Za-z0-9-_]*){3,63}\.)+([A-Za-z0-9]{2,4}\.?)+){1,255}$',0)=1
)
);
Valid IP Address (no leading zeros)
CREATE TABLE Computers
( computer_name
varchar(100),
ip_address
varchar(20)
CHECK (
mdq.RegexIsMatch([ip_address],
'^(?:(?:25[0-5]|2[0-4][0-9]|1[0-9][0-9]|[1-9]?[0-9])\.){3}(?:25[0-5]|2[0-4][0-9]|1[0-9][0-9]|[1-9]?[0-9])$',0)=1
)
);
As you can see, these mdq.regex functions add a whole new world of possibilities to your SQL Server instance. Thanks for reading!
--ab
Last Updated: 9/12/2013 (Posted)
I updated this article on 12/7/2013 to include a link to instructions for enabling CLR Integration as and an updated link for the instructions to create the [Microsoft.MasterDataServices.DataQuality] assembly.
This is the second article in my series, mdq.regex CLR Functions. In my previous article about mdq.regex functions, mdq.regex CLR Functions -- Part 1 I introduced mdq.RegexIsMatch, a scalar CLR function that returns a bit based on if a regex pattern is matched. Today we will review mdq.RegexMatches.
mdq.RegexMatches is a Table Valued CLR function. Like mdq.RegexIsMatch, setup is fast, easy and this CLR was developed, tested, tuned and QA’d by Microsoft.
To create this function you will need:
See this article for details on enabling CLR integration and this article for the steps to create the assembly.
Run the following DDL to create mdq.RegexMatches:
CREATE FUNCTION mdq.[RegexMatches](@input [nvarchar](4000), @pattern [nvarchar](4000), @mask [tinyint] = 0)
RETURNS TABLE
( [Sequence] [int] NULL,
[Token] [nvarchar](4000) NULL)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [Microsoft.MasterDataServices.DataQuality].[Microsoft.MasterDataServices.DataQuality.SqlClr].[RegexMatches]
GO
mdq.RegexMatches searches an input string for all occurrences of a regular expression and returns all the successful matches. This function uses the regular expression functionality of the Matches method in the Microsoft .NET Framework.
mdq.RegexMatches (input,pattern,mask)
input
Is the input string for which to find matches. input is nvarchar(4000) with no default.
pattern
Is the regular expression pattern to use for matching. pattern is nvarchar(4000) with no default.
mask
Is the RegexOptions mask that specifies the behavior of the regular expression. For more information, see mdq.RegexMask (Transact-SQL).
In the first example below I am searching for numeric patterns. For the second example I will demonstrate how to search for records based on a word pattern. We will conclude with a technique for querying a table for valid and invalid EMail addresses.
mdq.regexMatches to find numeric patterns
-- (1) Let's find this pattern: (one or
more numbers)-(one or more numbers)-(one or more numbers) [e.g. 222-111-9999]
SELECT Token
AS pattern
FROM clr.RegexMatches(N'XYXYXYXYYX11-222-333XYXYXYXYXY', N'\d+-\d+-\d+', 0);
GO
-- (2) Let's find patterns of one or more numbers
DECLARE @text
varchar(1000)='1 is one, 2 is two and 3 makes three',
@find_cons_digits varchar(20)='\d+';
SELECT Sequence, Token AS value
FROM clr.RegexMatches(@text, @find_cons_digits, 0);
GO
-- (3) Let's find sequenses of two
consecutive numbers
DECLARE @text
varchar(1000)='1 is one, 2 is two and 3 makes three',
@find_cons_digits varchar(20)='\d+';
SELECT @text='1 is zero, 11 is one, 22 is two... 345678 makes three, four
and five',
@find_cons_digits='\d{2}';
SELECT Sequence, Token AS value
FROM clr.RegexMatches(@text, @find_cons_digits, 0);
GO
mdq.regexMatches to find word patterns
-- (4) Let's perform a word count
DECLARE @quote varchar(1000)='The world is a dangerous place to live; not because of the people who are evil, but because of the people who dont do anything about it. --Albert Einstein',
@quote2 varchar(100)='apple orange orange pear',
@word_count varchar(20)='\b(\w+)',
@ab_count varchar(20)='\b([ab]\S+)',
@find_duplicates varchar(50)='\b(\w+?)\s\1\b';
SELECT COUNT(*) AS [word count]
FROM clr.RegexMatches(@quote,@word_count,0);
-------------------------------------------------------
-- (5) Let's find how many words begin with A or B
SELECT COUNT(*) AS [words that begin with A or B]
FROM clr.RegexMatches(@quote, @ab_count,0);
-- (6) Let's find repeated words
SELECT @quote2 AS quote, Token AS [repeated words]
FROM clr.RegexMatches(@quote2,@find_duplicates,0);
-- (7) Let's normalize a string, a series of numbers or both:
SELECT Sequence, Token AS val
FROM clr.RegexMatches(N'NormalizeMe', N'([a-zA-z])', ''); --string
SELECT Sequence, Token AS val
FROM clr.RegexMatches(N'54321', N'\d', ''); --series of numbers
SELECT Sequence, Token AS val
FROM clr.RegexMatches(N'text1234', N'([a-zA-z0-9])', ''); --text and numbers
Query that returns valid Email addresses and one that only returns invalid addresses:
IF OBJECT_ID('tempdb..#customer_email') IS NOT NULL
DROP TABLE #customer_email;
CREATE TABLE #customer_email
( email_id int identity primary key,
cust_id int NOT NULL,
email nvarchar(100) unique NOT NULL);
INSERT INTO #customer_email (cust_id, email)
SELECT 1,'john.doe@a+++cme. com' UNION ALL
SELECT 1,'JohnDoe@somewhere.com' UNION ALL
SELECT 2,'Sally.Smith@Microsoft' UNION ALL
SELECT 3,'AlChurch@turning.net' UNION ALL
SELECT 4,'JaneDoe@aol.com';
GO
DECLARE @validEmail nvarchar(1000);
SET @validEmail='^(([a-zA-Z0-9!#\$%\^&\*\{\}''`\+=-_\|/\?]+(\.[a-zA-Z0-9!#\$%\^&\*\{\}''`\+=-_\|/\?]+)*){1,64}@(([A-Za-z0-9]+[A-Za-z0-9-_]*){1,63}\.)*(([A-Za-z0-9]+[A-Za-z0-9-_]*){3,63}\.)+([A-Za-z0-9]{2,4}\.?)+){1,255}$';
-- (1) This will return records with only valid EMail addresses
SELECT e. *
FROM #customer_email e
CROSS APPLY clr.RegexMatches(e.email,@validEmail, 0);
-- (2) This will return records with only invalid EMail addresses
SELECT e.email_id, e.cust_id, e.email
FROM #customer_email e
EXCEPT
SELECT ex.email_id, ex.cust_id, ex.email
FROM #customer_email ex
CROSS APPLY clr.RegexMatches(ex.email, @validEmail, 0);
DROP TABLE #customer_email;
... EMail Query results:
Today we reviewed the steps for setting up and using mdq.RegexMatches. Thanks for reading!
--ab
Last Updated: 12/7/2013 (minor updates, repaired broken links)
This is a great article and a must-read for any DBA who has dealt with out-of-control transaction log growth. Enjoy!
Managing the SQL Server Transaction Log: Dealing with Explosive Log Growth
--ab
Last Updated: 5/1/2013 (Posted)
CREATE
FUNCTION dbo.NGrams8K (@string
varchar
(
8000), @n
int
)
/********************************************************************
Created by: Alan Burstein
Created on: 3/10/2014
Last Updated on: 09/09/2015
n-gram
defined:
In the fields of computational linguistics and probability,
an n-gram is a contiguous sequence of n items
from a given
sequence of text or speech. The items can be
phonemes, syllables,
letters, words or base pairs according to the
application.
For more information see: http://en.wikipedia.org/wiki/N-gram
Use:
Outputs a stream of tokens based on an input string.
Similar to mdq.nGrams:
http://msdn.microsoft.com/en-us/library/ff487027(v=sql.105).aspx.
Except it only returns characters as long as K.
nGrams8K also includes the position of the
"Gram" in the string.
Revision History:
Rev 00 - 03/10/2014 Initial Development - Alan
Burstein
Rev 01 - 05/22/2015 Removed DQS N-Grams
functionality,
improved iTally - Alan Burstein
Rev 02 - 05/22/2015 Changed TOP logic to
remove implicit conversion
- Alan Burstein
Rev 03 - 9/9/2015 Added logic to only return
values if @n is greater
than 0 and
less then length of @string - Alan Burstein
********************************************************************/
RETURNS
TABLE
WITH
SCHEMABINDING
AS
RETURN
WITH
L1(
N)
AS
(
SELECT 1
FROM
(
VALUES
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)
)
t(
N)
),
iTally
(
N) AS
(
SELECT
TOP
(
CONVERT(BIGINT,(DATALENGTH(@string)-(@n-1)),0))
ROW_
NUMBER(
)
OVER
(
ORDER
BY
(
SELECT
NULL))
FROM L1 a CROSS JOIN L1 b
-- add two more
cross joins to support varchar(max)
)
SELECT
position = N,
token = SUBSTRING(@
string,N,@n
)
FROM
iTally
WHERE @n > 0 AND @n <=
DATALENGTH
(
@string);
GO
/********************************************************************
Created by: Alan
Burstein
Created on: 3/10/2013
Last Updated on: 09/20/2015
Use:
Returns the longest common substring between two strings.
Revision History:
Rev 00 - 03/10/2013 Initial Development - Alan
Burstein
Rev 03 - 09/20/2015 Performance tuned using
NGrams8K - Alan Burstein
********************************************************************/
CREATE
FUNCTION dbo.LCSS8K
(
@string1
varchar
(
8000),
@string2
varchar
(
8000)
)
RETURNS
TABLE
WITH
SCHEMABINDING
AS
RETURN
WITH
Strings AS
(
SELECT
String1 = CASE WHEN
LEN
(
@string1)>LEN(@string2) THEN @string1 ELSE @string2 END,
String2 = CASE WHEN
LEN
(
@string1)>LEN(@string2) THEN @string2 ELSE @string1 END
),
I(
N)
AS (SELECT position FROM
Strings CROSS APPLY
dbo.NGrams8K(String2,1))
SELECT
TOP (1) WITH TIES
TokenLength
= I.N,
NG.Token
FROM
I CROSS APPLY Strings s CROSS
APPLY
dbo.NGrams8K(
String2,I.N) NG
WHERE
CHARINDEX
(
NG.token,String1) > 0
ORDER
BY N DESC;
GO
Another problem solved without cursors, loops or recursive CTEs. Let's mark this one up as another win for the Tally Table. Thanks for reading!
--ab
Last Updated: 09/28/2015
