SQL で数式を評価 (完全版 + α)

大阪で働く友人 mi との合作*1で不完全版が完全版に！
やっぱり mi はすごかった！！

WITH
  Input(id, str) AS (
    -- idと数式を渡す    
    SELECT 1, '1 + 3 / -8'
    UNION ALL SELECT 2, '2*3*4*5+99'
    UNION ALL SELECT 3, '4 * (9 - 4) / (2 * 6 - 2) + 8'
    UNION ALL SELECT 4, '1 + ((123 * 3 - 69) / 100)'
    UNION ALL SELECT 5, '2.45/8.5*9.27+(5*0.0023)'
  )
, Separators(i, ch, str_src, priority) AS (
    SELECT 1, '-', 1, 1
    UNION ALL SELECT 2, '+', 1, 1
    UNION ALL SELECT 3, '*', 1, 2
    UNION ALL SELECT 4, '/', 1, 2
    UNION ALL SELECT 5, '(', 0, 0
    UNION ALL SELECT 6, ')', 0, 0
  )
, SeparatorsStrSrc(str, i) AS (
    SELECT CAST('[' AS varchar(max)), 0
    UNION ALL
    SELECT
        str + ch
      , SSS.i + 1
    FROM
        SeparatorsStrSrc SSS
          INNER JOIN Separators S ON SSS.i = S.i - 1
    WHERE
        str_src <> 0
  )
, SeparatorsStr(str) AS (
    SELECT str + ']' FROM SeparatorsStrSrc
    WHERE i = (SELECT COUNT(*) FROM Separators WHERE str_src <> 0)
  )
, ExprElementsSrc(id, i, tmp, ele, pre_ch, input_str) AS (
    -- 数式を要素に分割する
    SELECT
        id
      , 1   -- 要素に連番を振る
      , CAST(LEFT(str, 1) AS varchar(max))
      , CAST('' AS varchar(max))
      , CAST(' ' AS char(1))
      , SUBSTRING(str, 2, LEN(str))
    FROM
        Input
    UNION ALL
    SELECT
        id
      , CASE ele
        WHEN '' THEN i
                ELSE i + 1
        END
      , CAST(
          CASE
          WHEN LEFT(input_str, 1) = ' '
            THEN ''
          WHEN tmp = '-'
            THEN CASE
                 WHEN pre_ch LIKE (SELECT str FROM SeparatorsStr)
                   THEN tmp + LEFT(input_str, 1)
                   ELSE LEFT(input_str, 1)
                 END
          WHEN LEFT(input_str, 1) IN (SELECT ch FROM Separators)
               OR
               tmp IN (SELECT ch FROM Separators)
            THEN LEFT(input_str, 1)
            ELSE tmp + LEFT(input_str, 1)
          END
        AS varchar(max))
      , CAST(
          CASE
          WHEN LEFT(input_str, 1) = ' '
            THEN tmp
          WHEN LEFT(input_str, 1) = '-'
            THEN CASE
                 WHEN tmp IN (SELECT ch FROM Separators)
                   THEN tmp
                   ELSE ''
                 END
          WHEN LEFT(input_str, 1) IN (SELECT ch FROM Separators)
               OR
               tmp IN (SELECT ch FROM Separators)
            THEN CASE
                 WHEN tmp = '-' AND pre_ch LIKE (SELECT str FROM SeparatorsStr)
                   THEN ''
                   ELSE tmp
                 END
            ELSE ''
          END
        AS varchar(max))
      , CAST(LEFT(ele, 1) AS char(1))
      , SUBSTRING(input_str, 2, LEN(input_str))
    FROM
        ExprElementsSrc
    WHERE
        input_str <> ''
        OR
        tmp <> ''
  )
, ExprElements(id, i, ele) AS (
    SELECT
        id
      , i
      , ele
    FROM
        ExprElementsSrc
    WHERE
        ele <> ''
  )
, Scanner(id, i, val) AS (
    SELECT
        id
      , i
      , CAST(ele AS varchar(max))
    FROM
        ExprElements
    WHERE
        ele <> ''
    -- EOFを示す行を追加する
    UNION ALL
    SELECT
        id
      , MAX(i) + 1
      , NULL
    FROM
        ExprElements
    GROUP BY
        id
  )
, Operator(op, priority) AS (
    SELECT
        ch
      , priority 
    FROM
        Separators
    WHERE
        priority <> 0
  )
, Calc(id, c, i, pop_count, s0, s1, s2, stack, status) AS (
    -- 初期値
    SELECT
        Scanner.id
      , 1
      , 1
      , 0
      , CAST(scanner.val AS varchar(max))
      , CAST(NULL AS varchar(max))
      , CAST(NULL AS varchar(max))
      , CAST('' AS varchar(max))
      , CAST('init' AS varchar(max))
    FROM
        Scanner
    WHERE
        Scanner.i = 1
    -- 演算
    UNION ALL
    SELECT
        Calc.id
      , Calc.c + 1
      , Calc.i
      , 3
      , NULL
      , NULL
      , NULL
      , CASE Calc.s1
        WHEN '+' THEN CAST(CAST(Calc.s2 AS real) + CAST(Calc.s0 AS real) AS varchar(max))
        WHEN '-' THEN CAST(CAST(Calc.s2 AS real) - CAST(Calc.s0 AS real) AS varchar(max))
        WHEN '*' THEN CAST(CAST(Calc.s2 AS real) * CAST(Calc.s0 AS real) AS varchar(max))
        WHEN '/' THEN CAST(CAST(Calc.s2 AS real) / CAST(Calc.s0 AS real) AS varchar(max))
                 ELSE NULL
        END
          + ' '
          + stack
      , CAST('calc ' + Calc.s1 AS varchar(max))
    FROM
        Calc
          INNER JOIN Scanner NextVal ON Calc.id = NextVal.id
                                          AND Calc.i + 1 = NextVal.i
    WHERE
        Calc.pop_count = 0
          AND ISNUMERIC(Calc.s2) = 1
          AND Calc.s1 IN (SELECT op FROM Operator)
          AND ISNUMERIC(Calc.s0) = 1
          AND (SELECT priority FROM Operator WHERE op = Calc.s1)
            >= COALESCE((SELECT priority FROM Operator WHERE op = NextVal.val), 0)
    -- paren
    UNION ALL
    SELECT
        Calc.id
      , Calc.c + 1
      , Calc.i
      , 3
      , NULL
      , NULL
      , NULL
      , s1 + ' ' + stack
      , CAST('paren' AS varchar(max))
    FROM
        Calc
    WHERE
        pop_count = 0
          AND s2 = '('
          AND ISNUMERIC(s1) = 1
          AND s0 = ')'
    -- pop
    UNION ALL
    SELECT
        Calc.id
      , Calc.c + 1
      , Calc.i
      , Calc.pop_count - 1
      , s1
      , s2
      , CASE
        WHEN LEN(stack) > 0
          THEN SUBSTRING(stack, 1, CHARINDEX(' ', stack) - 1)
          ELSE NULL
        END
      , CASE
        WHEN LEN(stack) > 0
          THEN SUBSTRING(stack, CHARINDEX(' ', stack) + 1, LEN(stack))
          ELSE ''
        END
      , CAST('pop' AS varchar(max))
    FROM
        Calc
    WHERE
        Calc.pop_count > 0
    -- 読み込み
    UNION ALL
    SELECT
        Calc.id
      , Calc.c + 1
      , Calc.i + 1
      , Calc.pop_count
      , CAST(NextVal.val AS varchar(max))
      , s0
      , s1
      , COALESCE(s2, '') + ' ' + stack
      , CAST('read' AS varchar(max))
    FROM
        Calc
          INNER JOIN Scanner NextVal ON Calc.id = NextVal.id
                                          AND Calc.i + 1 = NextVal.i
    WHERE
        NextVal.val IS NOT NULL -- 次の要素がないなら読み込まない
          AND Calc.pop_count = 0
          AND (
            (Calc.s0 IS NULL OR Calc.s1 IS NULL OR Calc.s2 IS NULL) -- 下の比較でスタックの中がNULLだったときの保険（スタックの深さを持ったほうがいいのか？）
            OR 
            -- 計算していない
            NOT(
              ISNUMERIC(Calc.s2) = 1
                AND Calc.s1 IN (SELECT op FROM Operator)
                AND ISNUMERIC(calc.s0) = 1
                AND (SELECT priority FROM Operator WHERE op = Calc.s1)
                  >= COALESCE((SELECT priority FROM Operator WHERE op = NextVal.val), 0)
            )
              -- 括弧でない
              AND NOT(s2 = '(' AND ISNUMERIC(s1) = 1 AND s0 = ')')
          )
  )
SELECT
    Calc.id
  , Input.str
  , Calc.s0 AS result
FROM
    Calc
      INNER JOIN Input ON Calc.id = Input.id
WHERE
    Calc.c = (SELECT MAX(c) FROM Calc calc2
              WHERE Calc.id = Calc2.id)
ORDER BY
    id

役割分担としては、Separators と SeparatorsStrSrc と SeparatorsStr と ExprElementsSrc と ExprElements が自分で、Scanner と Operator と Calc(でかいw) が mi です。
しかも Separators と SeparatorsStrSrc と SeparatorsStr は、演算子周りの柔軟さを失わないようにするために後から付けたもので、オリジナル版では Operator は

, Operator(op, priority) AS (
    SELECT '+', 1
    UNION ALL SELECT '-', 1
    UNION ALL SELECT '*', 2
    UNION ALL SELECT '/', 2
  )

な感じでした。
てことでほとんど mi が作ったようなものです。

再帰部分で UNION ALL を複数回使うとか、まったく考えつきもしなかった方法をさらっと使う、そこにしび(ry
特筆すべきは演算子周りの扱いのうまさ。

演算子の優先順位を変更したければ、Separators(元 Operator) の priority をいじるだけ。
タイトルに + α って付けたのは、実はこの SQL、かけ算と割り算を足し算と引き算より優先してくれるんです。
元々のお題的には不要な機能なんだけど、元々のお題に対応したいときでもかけ算と割り算の優先順位を 1 に変更するだけで対応可能というすばらしさ。

更に、演算子の追加も基本的には Separators(元 Operator) に追加して、「-- 演算」というコメントが付いてる UNION ALL で連結している SELECT に処理を書くだけ。
素晴らしい！

あと、ISNUMERIC なんて構文 (関数ではないらしい) も初めて知りました。
なんか '+' とか '-' でも 1 を返すのが微妙っちゃ微妙だけど。

あ、実行結果はこんな感じ。

id	str	result
1	1 + 3 / -8	0.625
2	2*3*4*5+99	219
3	4 * (9 - 4) / (2 * 6 - 2) + 8	10
4	1 + ((123 * 3 - 69) / 100)	4
5	2.45/8.5*9.27+(5*0.0023)	2.68344

かけ算と割り算の priority も 1 にすると、

id	str	result
1	1 + 3 / -8	-0.5
2	2*3*4*5+99	219
3	4 * (9 - 4) / (2 * 6 - 2) + 8	10
4	1 + ((123 * 3 - 69) / 100)	4
5	2.45/8.5*9.27+(5*0.0023)	2.68344

こう。ちゃんと数式を評価できてますね！
すごい！！

*1:自分でも完全版書いたんだけど、こっちの方がすごいのでこっちを採用