firebird - 从字符串列中提取整数值和附加文本

Question

我正在将 BDE 查询(Paradox)转换为 Firebird(2.5，而不是 3.x)，并且其中有一个非常方便的转换:

select TRIM('      1') as order1, CAST('      1' AS INTEGER) AS order2    --> 1
select TRIM('  1 bis') as order1, CAST('  1 bis' AS INTEGER) AS order2    --> 1

然后按转换值排序，然后修剪值(ORDER order2，order1)为我提供我需要的结果:

1
1 bis
2 ter
100
101 bis

但是，在 Firebird 中转换不正确的整数会引发异常，并且我没有找到任何方法来提供相同的结果。我想我可以判断一个数字是否存在，如下所示，但我找不到提取它的方法。

TRIM('    1 bis') similar to '[ [:ALPHA:]]*[[:DIGIT:]]+[ [:ALPHA:]]*' 

<小时/>

[编辑]

我必须处理文本在数字之前的情况，因此使用@Arioch'The 的触发器，我运行得很好:

SET TERM ^ ;
CREATE TRIGGER SET_MYTABLE_INTVALUE FOR MYTABLE ACTIVE
BEFORE UPDATE OR INSERT POSITION 0
AS 
DECLARE I INTEGER;
DECLARE S VARCHAR(13);
DECLARE C VARCHAR(1);
DECLARE R VARCHAR(13);
BEGIN 
  IF (NEW.INTVALUE is not null) THEN EXIT;
  S = TRIM( NEW.VALUE );
  R = NULL;
  I = 1;
  WHILE (I <= CHAR_LENGTH(S)) DO
  BEGIN
    C = SUBSTRING( S FROM I FOR 1 );
    IF ((C >= '0') AND (C <= '9')) THEN LEAVE;
    I = I + 1;
  END
  WHILE (I <= CHAR_LENGTH(S)) DO
  BEGIN
    C = SUBSTRING( S FROM I FOR 1 );
    IF (C < '0') THEN LEAVE;
    IF (C > '9') THEN LEAVE;
    IF (C IS NULL) THEN LEAVE;
    IF (R IS NULL) THEN R=C; ELSE R = R || C;
    I = I + 1; 
  END
  NEW.INTVALUE = CAST(R AS INTEGER);
END^
SET TERM ; ^

Answer 1

转换这样的表，您必须添加一个特殊的索引整数列来保存提取的整数数据。

注意，这个查询在使用“非常方便的转换”时实际上相当糟糕:您应该使用索引列对大量数据进行排序(排序)，否则您将执行缓慢并浪费大量内存/磁盘临时排序表。

因此，您必须添加一个额外的整数索引列并在查询中使用它。

下一个问题是如何填充该列。

最好是在将整个数据库和应用程序从 BDE 迁移到 Firebird 时执行一次。从那时起，让您的应用程序在输入新数据行时正确填充 varchar 和 integer 列。

然后，您的转换器应用程序可以完成一次转换。或者您可以使用可选的存储过程，它会使用此类和添加的列重复表。或者，您可以创建执行 block 来迭代表并更新其行，计算所述整数值。

How to SELECT a PROCEDURE in Firebird 2.5

如果您需要保留旧应用程序，仅插入文本列而不插入整数列，那么我认为您必须在 Firebird 中使用 BEFORE UPDATE OR INSERT 触发器，这将解析文本逐个字母的列值并从中提取整数。然后确保您的应用程序永远不会直接更改该整数列。

查看触发器示例:Trigger on Update Firebird

PSQL 语言文档:https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-psql.html

无论您是否编写过程或触发器来填充所述添加的整数索引列，您都必须对字符进行简单的循环，从第一个数字复制字符串到第一个非数字。

https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-functions-scalarfuncs.html#fblangref25-functions-string

https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-psql-coding.html#fblangref25-psql-declare-variable

类似的事情

CREATE TRIGGER my_trigger FOR my_table 
BEFORE UPDATE OR INSERT
AS 
DECLARE I integer;
DECLARE S VARCHAR(100);
DECLARE C VARCHAR(100);
DECLARE R VARCHAR(100);
BEGIN 
  S = TRIM( NEW.MY_TXT_COLUMN );
  R = NULL;
  I = 1;
  WHILE (i <= CHAR_LENGTH(S)) DO
  BEGIN
    C = SUBSTRING( s FROM i FOR 1 );
    IF (C < '0') THEN LEAVE;
    IF (C > '9') THEN LEAVE;
    IF (C IS NULL) THEN LEAVE;

    IF (R IS NULL) THEN R=C; ELSE R = R || C;
    I = I + 1; 
  END

  NEW.MY_INT_COLUMN = CAST(R AS INTEGER);
END;

在此示例中，您的ORDER order2, order1 将变为

SELECT ..... FROM my_table ORDER BY MY_INT_COLUMN, MY_TXT_COLUMN 

<小时/>

此外，您的列似乎实际上包含复合数据:整数索引和可选的文本后缀。如果是这样，那么您拥有的数据没有标准化，最好重新构建表。

CREATE TABLE my_table (
  ORDER_Int INTEGER NOT NULL,
  ORDER_PostFix VARCHAR(24) CHECK( ORDER_PostFix = TRIM(ORDER_PostFix) ),

  ......

  ORDER_TXT COMPUTED BY (ORDER_INT || COALESCE( ' ' || ORDER_PostFix, '' )),
  PRIMARY KEY (ORDER_Int, ORDER_PostFix )
);

当您将数据从 Paradox 移动到 Firebird 时 - 让您的转换器应用程序检查并将这些值(如“1 bis”)拆分为两个新列。

然后你的查询就像

SELECT ORDER_TXT, ...  FROM my_table ORDER BY ORDER_Int, ORDER_PostFix