Can I use wildcards in Firebird POSITION function

Question

I am using Firebird 2.1.

I have job order number that may have 1 or 2 alpha characters then 4 or 5 numbers then maybe a prefix with 1 alpha character and 2 numbers.

I want to extract the 4-5 digit number in the middle.

I tried the following to find the number char, but it returned 0:

POSITION('%[0-9]%',JOBHEADER.ORDERNUMBER,1) AS "FIRST NUMBER"

I am not sure if I can use wildcards with the POSITION function. I guess I could try and check the 2nd or 3rd character for a number, but I really need the wild card feature to then find the next alpha after I find the position of the first number. Or maybe there is another solution to extract the number.

I have found something simliar:

CASE WHEN SUBSTRING(ordernumber FROM 2 FOR 5) SIMILAR TO '[0-9]+'
     THEN SUBSTRING(ordernumber FROM 2 FOR 5)
     ELSE SUBSTRING(ordernumber FROM 3 FOR 5)
END as PROJECTNUMBER

But with the number possibly starting in the first 5 chars then a if/case statement starts getting quite big.

Answer 1

No you can't do this with POSITION. Position searches for the exact substring in a given string. However, with Firebird 3, you could use SUBSTRING with regular expressions to extract the value, for example:

substring(ordernumber similar '%#"[[:DIGIT:]]+#"%' escape '#')

The regular expression must cover the entire string, while the #" encloses the term to extract (the # is the explicitly defined escape symbol). You may need to use more complex patterns like [^[:DIGIT:]]*#"[[:DIGIT:]]+#"([^[:DIGIT:]]%)? to avoid edge-cases in greediness.

If you know the pattern is always 1 or 2 alpha, 4 or 5 digits you want to extract, possibly followed by 1 alpha and 2 numbers, you could also use [[:ALPHA:]]{1,2}#"[[:DIGIT:]]{4,5}#"([[:ALPHA:]][[:DIGIT:]]{1,2})?. If the pattern isn't matched null is returned.

See also:

• README.substring_similar.txt
• regular expression syntax

Be aware that the SQL standard regular expression syntax supported by Firebird is a bit odd, and less powerful than regular expressions common in other languages.

Using PSQL

To solve this using PSQL, under Firebird 2.1, you can use something like:

create or alter procedure extract_number(input_value varchar(50)) 
    returns (output_value bigint)
as
declare char_position integer = 0;
declare number_string varchar(20) = '';
declare current_char char(1);
begin
  while (char_position < char_length(input_value)) do
  begin
    char_position = char_position + 1;
    current_char = substring(input_value from char_position for 1);
    if ('0' <= current_char and current_char <= '9') then
    begin
      number_string = number_string || current_char;
    end
    else if (char_length(number_string) > 0) then
    begin
      -- switching from numeric to non-numeric, found first number occurrence in string
      leave;
    end
  end
  output_value = iif(char_length(number_string) > 0, cast(number_string as bigint), null);
end