In TSQL there is a limit to the way you can compare text strings. They're either equal or not. Sooner or later, usually when cleaning data, something more subtle is required!
When you compare two pieces of text, or strings, in SQL Server in an expression, you will get just get a value of 'true' returned if the two were the same, 'false' if they were not, or 'null' if one of the pieces of text was null.
A simple matter of an extra space character is often enough to tip the balance. This is quite unlike real life - If we look at two pieces of text we judge them to be the same, almost the same, quite similar, nothing like each other and many shades in-between. Surely, we need to quantify differences?
Text difference algorithms are as
old as the hills- but not in TSQL
In IT applications, there are several times when one needs more of a measure of the differences between text, than a simple 'yes they're the same/ no they are not'. A typical problem is in finding duplicates in database entries where the understanding of 'duplicate' allows for minor differences. Finding a reliable algorithm for quantifying similarity in text is quite hard, especially one that is set-based. TSQL has no native means to use regular expressions and other means of making life easier for this sort of work
I find this problem quite intriguing but I can't pretend to offer a solution that is anywhere near perfect. I'd rather like to throw it open to our readers as a good solution would be extraordinarily useful. I'm not at all convinced by 'soundex' algorithms- they don't seem to help much. I've had an eye out for a good solution to quantifying the differences between blocks of text but haven't come across anything yet.
I find that the solution, the difference counter, that I give below good enough for my purposes, though it sometimes gives a score for differences that I don't like. Try it yourselves with a variety of strings and you'll see it makes a pretty good attempt. It is, of course, slow, because of the tedium of breaking down text into words and white-space. In normal use, this is only done once, when importing text into the database, when it is placed in an 'inversion table'. One can use this data to test the similarity of the original text, which is much faster. Just so as to include those stuck on SQL Server 2000, I've made the function use a nTEXT parameter rather than a VARCHAR(MAX) though the latter would have made for a simpler routine
“Cleaning data is not an exact science”
In reality, every time one comes across a requirement where one has to check for differences, there are subtle requirements that are never the same. Cleaning data is not an exact science. I generally prefer to ignore 'white-space', including new-lines and punctuation, when checking for differences. My approach is to break down text into words and 'not-words', or white-space. I refer to these as different types of token. The table function I give below allows you to define a word in terms of the characters that make up a word. This is different in other languages. The routine is generally, though not always, much faster if one uses a 'number table' but I decided that creating one for this article was a distraction for the reader .
With the use of the 'parsing' table-function, I then do a simple outer join between the two collections of words, and count the number of times that the minimum 'best-fit' between words changes in the sequence of words. This is of course, an approximation: I should be using sliders and other devices that use iteration. At some point one has to hand over to the computer scientists. I tend to stop at the point where the routine does the job I want.
As a flourish, I've provided, at the end, a variation of the function that provides a single-row table giving the first point at which the two samples of text diverge. It is really just a by-product of the first routine but I slipped it in to give a suggestion of the many ways the routine can be adapted for particular purposes. It is surprisingly handy for applications such as summary reports of the latest changes made to stored procedures!
IF OBJECT_ID(N'dbo.uftWordTokens') IS NOT NULL
DROP FUNCTION dbo.uftWordTokens
GO
/*------------------------------------------------------------*/
CREATE FUNCTION [dbo].[uftWordTokens]
(
@string NTEXT,
@WordStartCharacters VARCHAR(255) = 'a-z',
@WordCharacters VARCHAR(255) = '-a-z'''
)
RETURNS @Results TABLE
(
SeqNo INT IDENTITY(1, 1),
Item VARCHAR(255),
TokenType INT
)
AS /*
This table function produces a table which divides up the words and
the spaces between the words in some text and produces a table of the
two types of token in the sequence in which they were found
*/
BEGIN
DECLARE @Pos INT, --index of current search
@WhereWeAre INT,--index into string so far
@ii INT, --the number of words found so far
@next INT, --where the next search starts
@size INT --the total size of the text
SELECT @ii = 0, @WhereWeAre = 1, @size = DATALENGTH(@string)
WHILE @Size >= @WhereWeAre
BEGIN
SELECT @pos = PATINDEX('%[' + @wordStartCharacters + ']%',
SUBSTRING(@string, @whereWeAre, 4000))
IF @pos > 0
BEGIN
IF @pos > 1
INSERT INTO @Results
( item, tokentype )
SELECT SUBSTRING(@String, @whereWeAre, @pos - 1), 2
SELECT @next = @WhereWeAre + @pos, @ii = @ii + 1
SELECT @pos = PATINDEX('%[^' + @wordCharacters + ']%',
SUBSTRING(@string, @next, 4000) + ' ')
INSERT INTO @Results
( item, tokentype )
SELECT SUBSTRING(@String, @next - 1, @pos), 1
SELECT @WhereWeAre = @next + @pos - 1
END
ELSE
BEGIN
IF LEN(REPLACE(
SUBSTRING(@String, @whereWeAre, 4000), ' ', '!'
)) > 0
INSERT INTO @Results
( item, tokentype )
SELECT SUBSTRING(@String, @whereWeAre, 4000), 2
SELECT @whereWeAre = @WhereWeAre + 4000
END
END
RETURN
END
/* Tests:
SELECT '[' + item + ']', tokentype
FROM dbo.uftWordTokens('This has
been relentlessly
,^----tested', DEFAULT, DEFAULT)
SELECT '[' + item + ']', tokentype
FROM dbo.uftWordTokens('This has been relentlessly tested !',
DEFAULT, DEFAULT)
SELECT item, tokentype
FROM dbo.uftWordTokens('This has been', DEFAULT, DEFAULT)
SELECT '[' + item + ']', tokentype
FROM dbo.uftWordTokens(' <!-- 23 343.43 <div>Hello there .... -->',
DEFAULT, DEFAULT)
*/
GO
IF OBJECT_ID(N'dbo.ufnDifferencesInText') IS NOT NULL
DROP FUNCTION dbo.ufiDifferencesInText
GO
/*------------------------------------------------------------*/
CREATE FUNCTION dbo.ufiDifferencesInText
(
@Sample NTEXT,
@comparison NTEXT
)
RETURNS INT
AS BEGIN
DECLARE @results TABLE
(
token_ID INT IDENTITY(1, 1),
sequenceNumber INT,
Sample_ID INT,
Item VARCHAR(255),
TokenType INT
)
/*
This function returns the number of differences it found between two pieces
of text
*/
INSERT INTO @results
( SequenceNumber, Sample_ID, Item, Tokentype )
SELECT seqno, 1, item, tokentype
FROM dbo.uftWordTokens(@sample, DEFAULT, DEFAULT)
INSERT INTO @results
( SequenceNumber, Sample_ID, Item, Tokentype )
SELECT seqno, 2, item, tokentype
FROM dbo.uftWordTokens(@comparison, DEFAULT, DEFAULT)
DECLARE @closestMatch TABLE
(
sequenceNumber INT,
skew INT
)
INSERT INTO @closestMatch
( sequencenumber, skew )
SELECT COALESCE(a.sequencenumber, b.sequencenumber),
COALESCEE(MIN(ABS(COALESCE(b.sequenceNumber, 1000)
- COALESCE(a.sequencenumber, 1000))),
-1)
FROM ( SELECT *
FROM @results
WHERE sample_ID = 1 AND tokentype = 1
) a FULL OUTER JOIN ( SELECT *
FROM @results
WHERE sample_ID = 2
AND tokentype = 1
) b ON a.item = b.item
GROUP BY COALESCE(a.sequencenumber, b.sequencenumber)
ORDER BY COALESCE(a.sequencenumber, b.sequencenumber)
RETURN ( SELECT SUM(CASE WHEN a.skew - b.skew = 0 THEN 0
ELSE 1
END)
FROM @closestmatch a INNER JOIN @closestMatch b
ON b.sequenceNumber = a.sequenceNumber + 2
)
END
GO
SELECT dbo.ufnDifferencesInText('I am a piece of text',
'I am a piece of text')
--0
SELECT dbo.ufnDifferencesInText('I am a piece of text',
'I am not a piece of text')
--1
SELECT dbo.ufnDifferencesInText('I am a piece of text',
'I am piece a a a of text')
--2
SELECT dbo.ufnDifferencesInText('I piece of text',
'I am a piece of text')
--1
SELECT dbo.ufnDifferencesInText('I am a pot of jam',
'I am a piece of text')
--3
SELECT dbo.ufnDifferencesInText('I am a pot of jam',
'I am a pot of jam beloved by humans')
--3
SELECT dbo.ufnDifferencesInText('I am a piece of text',
'text of piece a am I')
--4
SELECT dbo.ufnDifferencesInText('I am a piece of text',
'this is completely different')
--5
SELECT dbo.ufnDifferencesInText('I am a piece of text', '')
--5
SELECT dbo.ufnDifferencesInText('', 'I am a piece of text')
--5
SELECT dbo.ufnDifferencesInText('Call me Ishmael. Some years ago -- never mind how long precisely -- having little or no money in my purse, and nothing particular to interest me on shore, I thought I would sail about a little and see the watery part of the world. It is a way I have of driving off the spleen, and regulating the circulation. Whenever I find myself growing grim about the mouth; whenever it is a damp, drizzly November in my soul; whenever I find myself involuntarily pausing before coffin warehouses, and bringing up the rear of every funeral I meet; and especially whenever my hypos get such an upper hand of me, that it requires a strong moral principle to prevent me from deliberately stepping into the street, and methodically knocking people''s hats off -- then, I account it high time to get to sea as soon as I can. This is my substitute for pistol and ball. With a philosophical flourish Cato throws himself upon his sword; I quietly take to the ship. There is nothing surprising in this. If they but knew it, almost all men in their degree, some time or other, cherish very nearly the same feelings towards the ocean with me.'
, 'Call me Ishmael. Some years ago -- never mind how long precisely -- having little or no money in my purse, and nothing particular to interest me on shore, I thought I would sail about a little and see the watery part of the world. It is a way I have of driving off the spleen, and regulating the circulation. Whenever I find myself growing grim about the mouth; whenever it is a damp, drizzly November in my soul; whenever I find myself involuntarily pausing before coffin warehouses, and bringing up the rear of every funeral I meet; and especially whenever my hypos get such an upper hand of me, that it requires a strong moral principle to prevent me from deliberately stepping into the street, and methodically knocking people''s hats off -- then, I account it high time to get to sea as soon as I can. This is my substitute for pistol and ball. With a philosophical flourish Cato throws himself upon his sword; I quietly take to the ship. There is nothing surprising in this. If they but knew it, almost all men in their degree, some time or other, cherish very nearly the same feelings towards the ocean with me.')
-- =============================================
-- Description: A routine that returns a single-row which
-- gives the context of the first difference between two
-- strings
-- =============================================
IF OBJECT_ID(N'dbo.uftShowFirstDifference') IS NOT NULL
DROP FUNCTION dbo.uftShowFirstDifference
GO
CREATE FUNCTION uftShowFirstDifference
(
-- Add the parameters for the function here
@sample NTEXT,
@comparison NTEXT
)
RETURNS @result TABLE
(
-- Add the column definitions for the TABLE variable here
first VARCHAR(2000),
second VARCHAR(2000),
[where] INT
)
AS BEGIN
DECLARE @results TABLE
(
token_ID INT IDENTITY(1, 1),
sequenceNumber INT,
Sample_ID INT,
Item VARCHAR(255),
TokenType INT
)
INSERT INTO @results
( SequenceNumber, Sample_ID, Item, Tokentype )
SELECT seqno, 1, item, tokentype
FROM dbo.uftWordTokens(@sample, DEFAULT, DEFAULT)
INSERT INTO @results
( SequenceNumber, Sample_ID, Item, Tokentype )
SELECT seqno, 2, item, tokentype
FROM dbo.uftWordTokens(@comparison, DEFAULT, DEFAULT)
DECLARE @closestMatch TABLE
(
sequenceNumber INT,
skew INT
)
INSERT INTO @closestMatch
( sequencenumber, skew )
SELECT COALESCE(a.sequencenumber, b.sequencenumber),
COALESCE(MIN(ABS(COALESCE(b.sequenceNumber, 1000)
- COALESCE(a.sequencenumber, 1000))),
-1)
FROM ( SELECT *
FROM @results
WHERE sample_ID = 1 AND tokentype = 1
) a FULL OUTER JOIN ( SELECT *
FROM @results
WHERE sample_ID = 2
AND tokentype = 1
) b ON a.item = b.item
GROUP BY COALESCE(a.sequencenumber, b.sequencenumber)
ORDER BY COALESCE(a.sequencenumber, b.sequencenumber)
DECLARE @first VARCHAR(2000)
DECLARE @firstDifference INT
DECLARE @second VARCHAR(2000)
SELECT @FirstDifference = MIN(sequenceNumber)
FROM @closestMatch
WHERE skew <> 0
SELECT @first = '', @second = ''
SELECT TOP 10
@first = COALESCE(@First, '') + item
FROM @results
WHERE sample_ID = 1 AND sequenceNumber >= @FirstDifference
ORDER BY SequenceNumber
SELECT TOP 10
@second = COALESCE(@second, '') + item
FROM @results
WHERE sample_ID = 2 AND sequenceNumber >= @FirstDifference
ORDER BY SequenceNumber
INSERT INTO @result
( first, Second, [where] )
SELECT [first] = @First, [second] = @second,
[where] = @FirstDifference
RETURN
END
GO
SELECT *
FROM dbo.uftShowFirstDifference('I am a piece of text',
'I am a piece of text')
-- NULL
SELECT *
FROM dbo.uftShowFirstDifference('I am a piece of text',
'I am not a piece of text')
--a piece of text not a piece of text 5
SELECT *
FROM dbo.uftShowFirstDifference('I am a piece of text',
'I am piece a a a of text')
--a piece of text piece a a a of 5
SELECT *
FROM dbo.uftShowFirstDifference('I piece of text',
'I am a piece of text')
--piece of text am a piece of text 3
SELECT *
FROM dbo.uftShowFirstDifference('I am a pot of jam',
'I am a piece of text')
--pot of jam piece of text 7
SELECT *
FROM dbo.uftShowFirstDifference('I am a pot of jam',
'I am a pot of jam beloved by humans')
-- beloved by humans 13
SELECT *
FROM dbo.uftShowFirstDifference('I am a piece of text',
'text of piece a am I')
--I am a piece of text of piece a am 1
SELECT *
FROM dbo.uftShowFirstDifference('I am a piece of text',
'this is completely different')
--I am a piece of this is completely different 1
SELECT *
FROM dbo.uftShowFirstDifference('I am a piece of text', '')
--I am a piece of 1
SELECT *
FROM dbo.uftShowFirstDifference('', 'I am a piece of text')
-- I am a piece of 1