1

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 COLUMN
  FROM ORDERS

Example of data

IDENTIFIER          DESIRED OUTPUT
ANDRE 02869567995   02869567995
02869567995 MARIA   02869567995
028.695.67.995      02869567995
028695679-95        02869567995
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jonathan Livingston Seagull
  • 844
  • 10
  • 22
  • Could you please provide sample data and expected results? I have a hard time combining your need to _"use regex to extract only the numbers in a new column"_ and then trying to use a regex that checks if a substring is entirely digits. – Mark Rotteveel Sep 16 '21 at 08:01

2 Answers2

0

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.

In Firebird 3.0, you could also use a UDR or stored function to achieve this. Unfortunately, using the regular expression functionality available in Firebird alone will not be enough to solve this.

NOTE: The rest of the answer is based on the assumption to extract digits if the first 10 characters of string are digits. With the updated question, this assumption is no longer valid.

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.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
  • Unfortunately your query fail on obvious examples like 'a1b2c3d4e5f6g7i8j9k0l1m2n3o4p5' or '12345-67890'. It does extract "continuous group of digits from the middle of string" but I'm afraid it is not what @jonathan had on mind. – user13964273 Sep 16 '21 at 11:01
  • @user13964273 There is not enough information in the question to draw that conclusion. As presented in the example code of the question, the desire is to extract a value if the first 10 characters of a string are digits. That is also why I asked in a comment for example data and desired results just in case the desire is different, and I comment in my answer that it is based on that assumption. – Mark Rotteveel Sep 16 '21 at 11:09
  • Well... One of typical task for such questions is a search in denormalized data as entered by user. Example data in this case would be "10 kg", "100 EUR" and "10 000 ft". If this is true, your pattern should work. Another typical task is extracting numeric part of some kind of code or name. For example "EU132953", "AKB-12-32F", "Boeing-737" (and any kind of telephone numbers). For such data there is no solution in Firebird. – user13964273 Sep 16 '21 at 12:22
  • @user13964273 That depends on the exact requirements, with regard to `EU132953` and `Boeing-737`, the extraction can be done with `substring(IDENTIFIER similar '%#"[[:DIGIT:]]+#"%' escape '#')`, that would also work with `AKB-12-32F`, but you'd get `12`, which may or may not be what is wanted here. – Mark Rotteveel Sep 16 '21 at 12:34
  • Hi , I apologize for not being clear but I have updated my question with an example. Basically my problem is " extract only digits 0 to 9 regardless position". I know Regex can do the task but I don´t know how to use it in firebird sql. – Jonathan Livingston Seagull Sep 16 '21 at 19:37
  • 1
    @JonathanLivingstonSeagull In that case user13964273 is right, and this cannot be done, not without a custom UDF, stored procedure or (Firebird 3+) stored function. – Mark Rotteveel Sep 17 '21 at 08:53
-1

It is not possible in any version of Firebird.

user13964273
  • 1,012
  • 1
  • 4
  • 7
  • 1
    It is possible in Firebird 3 and higher, with [`SUBSTRING( ... SIMILAR ...)`](https://firebirdsql.org/file/documentation/html/en/refdocs/fblangref40/firebird-40-language-reference.html#fblangref40-scalarfuncs-substring-regexp) – Mark Rotteveel Sep 16 '21 at 07:58