Split по списку разделителей на T-SQL

Обычно T-SQL не часто используется для синтаксического анализа текста (например разбивки предложений на слова). Для этого больше подходят языки с поддержкой регулярных выражений. Но иногда все же возникает необходимость в разборе текста именно на стороне SQL-сервера. Скажем, при импорте текстовых файлов в БД с некоторым процессингом, когда недостачно функциональности bcp, BULK INSERT и SSIS-пакетов. Сегодня я поделюсь своей реализацией функции разбивки текста на фрагменты (split) по заданному списку разделителей. Работает в SQL Server 2008 и 2012.

Поскольку в T-SQL нет массивов или списков, вначале создадим табличный тип для хранения разделителей:

CREATE TYPE [dbo].[StringArray] AS TABLE(
 [String] nvarchar(max) NULL
)

Теперь функция:

CREATE FUNCTION [dbo].[SplitOn](
 @String nvarchar(max),
 @Separators StringArray readonly
)
RETURNS @Fragments TABLE (
 FragmentNumber int,
 Fragment nvarchar(max)
)
AS
BEGIN
 IF @String IS NULL RETURN
 
 IF NOT EXISTS (SELECT 1 FROM @Separators)
 BEGIN
  INSERT @Fragments SELECT 1, @String
  RETURN
 END
 
 DECLARE @SepLengths TABLE (
  Separator nvarchar(max), [Length] int
 )
 
 INSERT @SepLengths 
 SELECT [String], DATALENGTH([String]) / 2 FROM @Separators
 
 DECLARE @CurrentPosition int = 1, @OldPosition int = 1,
 @FragmentLength int = 0, @FragmentNumber int = 1,
 @Length int = DATALENGTH(@String) / 2, @Fragment nvarchar(max)
 
 WHILE @CurrentPosition IS NOT NULL
 BEGIN
  SELECT @CurrentPosition = NULL
  
  SELECT TOP 1 @CurrentPosition = [Position],
  @FragmentLength = [Length]
  FROM 
   (
    SELECT 
    [Position] = 
    CHARINDEX(Separator, @String, @OldPosition),
    [Length] FROM @SepLengths
   ) p
  WHERE [Position] > 0 ORDER BY [Position], [Length] DESC
  
  IF @CurrentPosition IS NULL
  BEGIN
   SELECT @Fragment = 
   SUBSTRING(@String, @OldPosition, 
   @Length - @OldPosition + 1)
   
   INSERT @Fragments
   SELECT @FragmentNumber, @Fragment
  END
  ELSE
  BEGIN
   SELECT @Fragment = 
   SUBSTRING(@String, @OldPosition,
   @CurrentPosition - @OldPosition)
   
   INSERT @Fragments SELECT @FragmentNumber, @Fragment
   
   SELECT @FragmentNumber = @FragmentNumber + 1,
   @OldPosition = @CurrentPosition + @FragmentLength
  END
 END
 
 RETURN
END

И пример использования:

DECLARE @String nvarchar(max) = N'Воры! Пожар! Убивают! Ничего подобного не случалось с тех самых пор, как он обосновался под Горой. Ярость дракона не поддается описанию. Разве что можно ее сравнить с бешенством какого-нибудь толстосума, привыкшего к своим богатствам и нежданно утратившего что-то, чем он почти не пользовался или чего вообще не трогал в течение многих лет. Смог выпустил из пасти струю пламени; пещера наполнилась дымом, Гора сотряслась от макушки до основания. Дракон попытался просунуть голову в отверстие. Убедившись, что оно слишком маленькое, Смог взревел — под землей словно прогремел гром — и ринулся по подземным коридорам к Парадным Вратам.'

DECLARE @Separators StringArray
INSERT @Separators VALUES ('.'), (','), ('!'), (';'), ('—')

SELECT * FROM dbo.SplitOn(@String, @Separators)

Результат выполнения:

Небольшое пояснение к алгоритму разбиения. По мере продвижения по строке вправо функция находит тот разделитель из списка, который встречается раньше других. Если есть вхождение двух разделителей с одной и той же позиции (такое возможно, когда один из разделителей входит с начала в другой), то выбирается самый длинный разделитель. Это демонстрируется следующим примером:

DECLARE @String nvarchar(max) = N'1,2,,3'

DECLARE @Separators1 StringArray, @Separators2 StringArray
INSERT @Separators1 VALUES (',')
INSERT @Separators2 VALUES (','), (',,')

SELECT * FROM dbo.SplitOn(@String, @Separators1)
SELECT * FROM dbo.SplitOn(@String, @Separators2)