How to extract digits from field using regex-CodePudding

I am using Firebird 2.5 and I have a field (called identifier) with mixed letters, numbers and special characters. I would like to use regex to extract only the numbers in a new column. I have tried something like below, but it is not working.

Any idea how I can achieve this using regex without using stored procedures or execute block

SELECT ORDER_ID,
        ORDER_DATE,
         SUBSTRING(IDENTIFIER FROM 1 TO 10) SIMILAR TO '^[0-9]{10}$' --- DESIRED EXTRACTION COLLUMN
  FROM ORDERS

CodePudding user response：

You cannot do this in Firebird 2.5, at least not without help from a UDF, or a (selectable) stored procedure. I'm not aware of third-party UDFs providing regular expressions, so you might have to write this yourself.

That said, if your need is exactly as shown in your question, that is only extract the first 10 characters from a string if they are all digits, then you could use:

case 
  when IDENTIFIER similar to '[[:DIGIT:]]{10}%'
    then substring(IDENTIFIER from 1 for 10)
end

^{(as an aside, the positional SUBSTRING syntax is from <start> for <length>, not from <start> to <end>)}

In Firebird 3.0 and higher, you can use SUBSTRING(... SIMILAR ...) with a SQL regular expression pattern. Assuming you want to extract 10 digits from the start of a string, you can do:

substring(IDENTIFIER similar '#"[[:DIGIT:]]{10}#"%' escape '#')

The #" delimits the pattern to extract (where # is a custom escape character as specified in the ESCAPE clause). The remainder of the pattern must match the rest of the string, hence the use of % here (in other cases, you may need to specify a pattern before the first #" as well.

See this dbfiddle for an example.

CodePudding user response：

It is not possible in any version of Firebird.