SQL Server 将搜索字符串解析为 Tokens

1. 表值函数

USE master;
GO

IF OBJECT_ID('master.dbo.Tokenize') IS NULL
  EXEC ('CREATE FUNCTION dbo.Tokenize () RETURNS @result TABLE (col INT) AS BEGIN RETURN END;');
GO
-- =============================================
-- Author     : FUWM
-- Create date: 20240802
-- Description: 将 @pattern 解析为 tokens
-- Grammar    : space   与
--              |       或（优先级：或 > 与）
--              !       非
--              ()      分组
--              ""      字面量（双引号中的控制字符将被视为普通字符）
-- =============================================
ALTER FUNCTION dbo.Tokenize (@pattern NVARCHAR(MAX))
RETURNS @tokens TABLE (
  idx  INT           -- token 序号
, lvl  INT           -- token 所在分组的深度
, typ  VARCHAR(20)   -- token 类型；Literal-字面量  And-与  Or-或  Not-非  Err-错误  End-终止
, body NVARCHAR(MAX) -- token 内容
)
AS
BEGIN
  DECLARE @i INT = 1, @j INT = 1, @ch NCHAR(1), @len INT = LEN(@pattern), @idx INT = 1, @lvl INT = 0, @isInOrSeg BIT, @isInDQ BIT = 0;

  WHILE @j <= @len
  BEGIN
    SET @ch = SUBSTRING(@pattern, @j, 1);

    IF @ch = N'"'
    BEGIN
      IF @isInDQ = 0
      BEGIN
        IF @j - @i >= 1
        BEGIN
          INSERT @tokens (idx, lvl, typ, body)
          VALUES (@idx, @lvl, 'Err', N'there should be no normal characters before the starting "\""');
          RETURN;
        END;

        SET @isInDQ = 1;
      END;
      ELSE
      BEGIN
        IF @j - @i <= 0
        BEGIN
          INSERT @tokens (idx, lvl, typ, body) VALUES (@idx, @lvl, 'Err', N'has empty literal');
          RETURN;
        END;

        INSERT @tokens (idx, lvl, typ, body) VALUES (@idx, @lvl, 'Literal', SUBSTRING(@pattern, @i, @j - @i));

        SET @idx = @idx + 1;
        SET @isInDQ = 0;
      END;

      SET @i = @j + 1;
    END;
    ELSE IF @isInDQ = 0
    BEGIN
      SET @isInOrSeg = CASE WHEN @ch IN (N' ', N'|', N')') THEN CASE WHEN EXISTS (
           SELECT 1
           FROM   @tokens t
           WHERE  t.idx > ISNULL((SELECT MAX(t.idx)FROM @tokens t WHERE t.lvl < @lvl), 0) AND t.lvl = @lvl AND t.typ = 'Or'
         ) THEN                                                      1
                                                                     ELSE 0
                                                                END
                       END;

      IF @ch = N' '
      BEGIN
        IF @j - @i >= 1
        BEGIN
          INSERT @tokens (idx, lvl, typ, body) VALUES (@idx, @lvl, 'Literal', SUBSTRING(@pattern, @i, @j - @i));
          SET @idx = @idx + 1;
        END;

        IF @isInOrSeg = 1
          SET @lvl = @lvl - 1;

        INSERT @tokens (idx, lvl, typ, body) VALUES (@idx, @lvl, 'And', NULL);

        SET @idx = @idx + 1;
        SET @i = @j + 1;
      END;
      ELSE IF @ch = N'|'
      BEGIN
        IF @isInOrSeg = 0
          SET @lvl = @lvl + 1;

        IF @j - @i >= 1
        BEGIN
          UPDATE t SET t.lvl = @lvl FROM @tokens t WHERE t.idx = @idx - 1 AND t.typ = 'Not';

          INSERT @tokens (idx, lvl, typ, body) VALUES (@idx, @lvl, 'Literal', SUBSTRING(@pattern, @i, @j - @i));

          SET @idx = @idx + 1;
        END;
        ELSE IF @isInOrSeg = 0
        BEGIN
          DECLARE @leftOperStarIdx INT = (SELECT MAX(t.idx)FROM @tokens t WHERE t.lvl < @lvl);
          SET @leftOperStarIdx = CASE WHEN @leftOperStarIdx IS NULL OR @leftOperStarIdx = @idx - 1 THEN @idx - 1 ELSE @leftOperStarIdx + 1 END;

          UPDATE t
          SET    t.lvl = t.lvl + 1
          FROM   @tokens t
          WHERE  t.idx >= @leftOperStarIdx OR t.idx = @leftOperStarIdx - 1 AND t.typ = 'Not';
        END;

        INSERT @tokens (idx, lvl, typ, body) VALUES (@idx, @lvl, 'Or', NULL);

        SET @idx = @idx + 1;
        SET @i = @j + 1;
      END;
      ELSE IF @ch = N'!'
      BEGIN
        INSERT @tokens (idx, lvl, typ, body) VALUES (@idx, @lvl, 'Not', NULL);

        SET @idx = @idx + 1;
        SET @i = @j + 1;
      END;
      ELSE IF @ch = N'('
      BEGIN
        SET @lvl = @lvl + 1;
        SET @i = @j + 1;
      END;
      ELSE IF @ch = N')'
      BEGIN
        IF @lvl <= 0
        BEGIN
          INSERT @tokens (idx, lvl, typ, body) VALUES (@idx, @lvl, 'Err', N'"(" is missing');
          RETURN;
        END;

        IF @j - @i >= 1
        BEGIN
          INSERT @tokens (idx, lvl, typ, body) VALUES (@idx, @lvl, 'Literal', SUBSTRING(@pattern, @i, @j - @i));
          SET @idx = @idx + 1;
        END;

        IF @isInOrSeg = 1
          SET @lvl = @lvl - 1;

        SET @lvl = @lvl - 1;
        SET @i = @j + 1;
      END;
    END;

    IF @j = @len
    BEGIN
      IF @isInDQ = 1
      BEGIN
        INSERT @tokens (idx, lvl, typ, body) VALUES (@idx, @lvl, 'Err', N'ending "\"" is missing');
        RETURN;
      END;

      IF @j - @i >= 0
      BEGIN
        INSERT @tokens (idx, lvl, typ, body) VALUES (@idx, @lvl, 'Literal', SUBSTRING(@pattern, @i, @j - @i + 1));
        SET @idx = @idx + 1;
      END;

      SET @isInOrSeg = CASE WHEN EXISTS (
      SELECT 1
      FROM   @tokens t
      WHERE  t.idx > ISNULL((SELECT MAX(t.idx)FROM @tokens t WHERE t.lvl < @lvl), 0) AND t.lvl = @lvl AND t.typ = 'Or'
    ) THEN                  1
                            ELSE 0
                       END;

      IF @isInOrSeg = 1
        SET @lvl = @lvl - 1;

      IF @lvl <> 0
      BEGIN
        INSERT @tokens (idx, lvl, typ, body) VALUES (@idx, @lvl, 'Err', N'")" is missing');
        RETURN;
      END;

      INSERT @tokens (idx, lvl, typ, body) VALUES (@idx, @lvl, 'End', NULL);
      SET @idx = @idx + 1;
    END;

    SET @j = @j + 1;
  END;

  RETURN;
END;
GO

2. 用法示例

DECLARE @tokens TABLE (idx INT, lvl INT, typ VARCHAR(20), body NVARCHAR(MAX));
INSERT @tokens (idx, lvl, typ, body)
SELECT t.idx, t.lvl, t.typ, t.body FROM master.dbo.Tokenize(N'a b|c d d1|(e|f g)|h') t;

DECLARE @err NVARCHAR(MAX) = (
          SELECT   TOP 1
                   CASE WHEN LEN(t.body) > 0 THEN t.body ELSE N'unknown' END
          FROM     @tokens t
          WHERE    t.typ = N'Err'
          ORDER BY t.idx DESC
        );

IF LEN(@err) > 0
BEGIN
  RAISERROR(@err, 16, 1);
  RETURN;
END;

SELECT (
         SELECT    CASE WHEN cur.lvl <> ISNULL(prv.lvl, 0) THEN
           (
             SELECT CASE WHEN cur.lvl > ISNULL(prv.lvl, 0) THEN N'(' ELSE N')' END
             FROM   master.dbo.spt_values spt WITH (NOLOCK)
             WHERE  spt.type = 'P' AND spt.number < CASE WHEN cur.lvl > ISNULL(prv.lvl, 0) THEN cur.lvl - ISNULL(prv.lvl, 0)ELSE prv.lvl - cur.lvl END
             FOR XML PATH(''), TYPE
           ).value('.', 'NVARCHAR(MAX)')
                        ELSE N''
                   END + CASE cur.typ
                              WHEN N'Literal' THEN 'a.col LIKE ''%' + cur.body + N'%'''
                              WHEN N'And' THEN N' AND '
                              WHEN N'Or' THEN N' OR '
                              WHEN N'Not' THEN N'NOT '
                              WHEN N'End' THEN N''
                         END
         FROM      @tokens cur
         LEFT JOIN @tokens prv ON prv.idx = cur.idx - 1
         FOR XML PATH(''), TYPE
       ).value('.', 'NVARCHAR(MAX)');