Extract integer value from string column with additional text

I'm converting a BDE query (Paradox) to a Firebird (2.5, not 3.x) and I have a very convenient conversion in it:

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

Then ordering by the cast value then the trimmed value (ORDER order2, order1) provide me the result I need:

1
1 bis
2 ter
100
101 bis

However, in Firebird casting an incorrect integer will raise an exception and I did not find any way around to provide same result. I think I can tell if a number is present with something like below, but I couldn't find a way to extract it.

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

[EDIT]

I had to handle cases where text were before the number, so using @Arioch'The's trigger, I got this running great:

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 ; ^

Solution

Converting such a table, you have to add a special indexed integer column for keeping the extracted integer data.

Note, this query while using "very convenient conversion" is actually rather bad: you should use indexed columns to sort (order) large amounts of data, otherwise you are going into slow execution and waste a lot of memory/disk for temporary sorting tables.

So you have to add an extra integer indexed column and to use it in the query.

Next question is how to populate that column.

Better would be to do it once, when you move your entire database and application from BDE to Firebird. And from that point make your application when entering new data rows fill BOTH varchar and integer columns properly.

One time conversion can be done by your convertor application, then. Or you can use selectable Stored Procedure that would repeat the table with such and added column. Or you can make Execute Block that would iterate through the table and update its rows calculating the said integer value.

How to SELECT a PROCEDURE in Firebird 2.5

If you would need to keep legacy applications, that only insert text column but not integer column, then I think you would have to use BEFORE UPDATE OR INSERT triggers in Firebird, that would parse the text column value letter by letter and extract integer from it. And then make sure your application never changes that integer column directly.

See a trigger example at Trigger on Update Firebird

PSQL language documentation: https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-psql.html

Whether you would write procedure or trigger to populate the said added integer indexed column, you would have to make simple loop over characters, copying string from first digit until first non-digit.

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

Something like that

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;

In this example your ORDER order2, order1 would become

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

Additionally, it seems your column actually contains a compound data: an integer index and an optional textual postfix. If so, then the data you have is not normalized and the table better be restructured.

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 )
);

When you would move your data from Paradox to Firebird - make your convertor application check and split those values like "1 bis" into two new columns.

And your query then would be like

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