Новости сайта "Упражнения по SQL (http://www.sql-ex.ru)" Выпуск 162 (2 ноября 2007 г.)

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 /*
Эта табличная функция создает таблицу, которая разделяет слова
и пробелы между словами в некотором тексте и создает таблицу
двух типов, взятых в последовательности, в которой они обнаруживаются
*/
   BEGIN
    DECLARE @Pos INT,    --индекс текущего поиска
      @WhereWeAre INT,--текущий индекс в строке
      @ii INT,    --текущее число найденных слов
      @next INT,  --откуда стартует поиск
      @size INT   --полный размер текста

    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

/* Тесты:
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(' ',
                          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
      )
/*
Эта функция возвращает число отличий, обнаруженное в двух текстах
*/
    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.')


-- =============================================
-- Описание: Процедура, которая возвращает единственную строку,
-- которая дает контекст первого различия двух строк
-- =============================================
IF OBJECT_ID(N'dbo.uftShowFirstDifference') IS NOT NULL
  DROP FUNCTION dbo.uftShowFirstDifference
GO
CREATE FUNCTION uftShowFirstDifference
  (
    -- Добавляем параметры для этой функции
    @sample NTEXT,
    @comparison NTEXT
  )
RETURNS @result TABLE
  (
    -- Добавляем определения столбцов для табличной переменной
    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