fb_regex

Firebird UDR library for regular expressions

★ 5 on GitHub

View on GitHub

Anyone may press Interesting, signed in or not. It helps others find this project.

Member rating

No ratings yet.

README

fb_regex

This is an extension library for RDBMS Firebird (>=3.0) to support regular expressions in SQL.

Basis

Library is implemented as а User Defined Routines (UDR) module of Firebird plugins architecture. Other side the DLL is compiled by Delphi 12 and uses the RegularExpression unit, which itself wraps PCRE 8.45. Thus to understand possibilities fb_regex regular expressions see PCRE documentation. In addition to PCRE 8.45, the conditional replacement syntax from PCRE2 is also implemented, as described here.

Routines

Routines are assembled into package regex. Pseudotype string marks any of string type char, varchar of any length or blob sub_type text. All the routines can accept and return any string type.

procedure matches

pascal

procedure matches(
    text    string    -- text to explore
  , pattern string    -- regular expression pattern to seek
)returns(
    number  integer   -- order number of found match started from 1
  , groups  string    -- string containing groups boundaries of found match
);

This is selective procedure, each result set row contains data of one match.

Output string groups contains groups boundaries of the match as semicolumn delimited pairs start:finish where start and finish are numeric positions in text started from 1.

procedure groups

pascal

procedure groups(
    groups  string   -- string containing groups boundaries of match
)returns(
    number  integer  -- order number of group. 0 group is the whole match
  , origin  integer  -- start position of the group in text
  , finish  integer  -- first position after the group in text
);

This is selective procedure, each row is one group. The procedure just parses string groups returned by procedure matches onto separate rows.

matches and groups procedures are designed to work in conjunction. You can control a result by standard SQL features. See а sample:

pascal

text : 'To be or not to be? Not to ask!'

pascal

task : find the second word follows 'not to'.

pascal

solution:
  select
        g.origin
      , g.finish
      , substring( :text from g.origin for g.finish - g.origin ) as word
    from
        regex.matches( :text, '(?i)not\s+to\s+(\w+)' ) m
      left join regex.groups( m.groups ) g
        on ( g.number = 1 )
    where
      m.number = 2

pascal

result:    
   ORIGIN       FINISH WORD
   ====== ============ =====
       28           31 ask

procedure find

pascal

procedure find(
    text    string    -- text to explore
  , pattern string    -- regular expression pattern to seek
  , amount  integer   -- maximum amount of rows to return
  , pass    integer   -- amount to skip first rows
)returns(
    number  integer   -- order number of found match started from 1
  , match   string    -- match string value
);

find is a more simple selective prоcedure. It works like matches but returns match string value instead of match boundaries and does not support groups to extract. Sample:

pascal

text : 'To be or not to be? Not to ask!'

pascal

task : find all 'to' with followed words. Skip the first match.

pascal

solution:
    select
        *
      from
        regex.find( :text, '(?i)to\s+(\w+)', null, 1 )

pascal

result:    
   NUMBER MATCH
   ====== ======
        1 to be
        2 to ask

function find_first

pascal

function find_first(
    text    string    -- text to explore
  , pattern string    -- regular expression pattern to seek
  , pass    integer   -- amount to skip first rows
)returns    string;   -- first match string value

find_first is a function that works like find but returns a single scalar result.

pascal

text : 'To be or not to be? Not to ask!'

pascal

task : get first 'not to' with first followed word.

pascal

solution:
    select
        regex.find_first( :text, '(?i)not to\s+(\w+)', null )
      from
        rdb$database

pascal

result:    
   FIND_FIRST
   ==========
   not to be

function replace

pascal

function replace(
    text        string    -- text to update
  , pattern     string    -- regular expression pattern to seek
  , replacement string    -- value to replace with
  , amount      integer   -- amount of matches to replace 
  , pass        integer   -- amount to skip first matches
)returns        string;   -- updated text

Function replace seeks matches in text and replaces its with replacement. Supports $n syntax in replacement, where n is a pattern group number. Supports conditional replacement expressions.

pascal

text : 'x = position (text,substring); y = position(a,b);'

pascal

task : find all the calls of 'position' and exchange its parameters. Format calls as position( a, b ).

pascal

solution:
    select
        regex.replace( :text, 'position\s*\(\s*(\w+)\s*,\s*(\w+)\s*\)', 'position( $2, $1 )', null,  null )
      from
        rdb$database

pascal

result:    
   REPLACE
   ==========
   x = position( substring, text ); y = position( b, a );

pascal

text : 'x = position (text,substring); y = position(a,b);'

pascal

task : find all the calls of 'position' and exchange its parameters. Do not any reformat.

pascal

solution:
    select
        regex.replace( :text, '(position\s*\(\s*)(\w+)(\s*,\s*)(\w+)(\s*\))', '$1$4$3$2$5', null,  null )
      from
        rdb$database

pascal

result:    
   REPLACE
   ==========
   x = position (substring,text); y = position(b,a);

pascal

text : 'x = run( value ); y = run();'

pascal

task : Change 'run' function to 'launch'. If function has no parameters set default one.

pascal

solution:
    select
        regex.replace( :text, 'run\(\s*?(\S.*?)?\s*?\)', 'launch( ${1:+$1:default} )', null,  null )
      from
        rdb$database

pascal

result:    
   REPLACE
   ==========
   x = launch( value ); y = launch( default );

procedure substitute

pascal

procedure substitute(
    text        string    -- text to update
  , pattern     string    -- regular expression pattern to seek
  , replacement string    -- value to replace with
  , amount      integer   -- amount of matches to replace 
  , pass        integer   -- amount to skip first matches
)returns(
    modified    string    -- updated text
  , replaced    integer   -- replaced matches count
)

This selective procedure does the same as the replace function and also returns count of replaced matches in a single row result set.

pascal

text : 'x = run( value ); y = run();'

pascal

task : Change 'run' function to 'launch'. If function has no parameters set default one. Show replaced functions count.

pascal

solution:
    select
          modified
        , replaced
      from
        regex.substitute( :text, 'run\(\s*?(\S.*?)?\s*?\)', 'launch( ${1:+$1:default} )', null,  null )

pascal

result:    
   MODIFIED                                    REPLACED
   =========================================== ========
   x = launch( value ); y = launch( default );        2

procedure split

pascal

procedure split(
    text      string    -- text to explore
  , separator string    -- regular expression pattern to seek separators
)returns(
    number    integer   -- part order number  
  , part      string    -- part between separators
);

split cuts text onto parts delimited by separator. Since separator is not a simple string but a regular exspression do not forget to escape special symbols if any.

pascal

text : 'Hamlet. To be or not to be? Not to ask!'

pascal

task : split text onto parts delimited by ".", "?" or "!".

pascal

solution:
    select
        *
      from
        regex.split( :text, '[.?!]' )

pascal

result:    
     NUMBER SPLIT
   ======== ========================
          1 Hamlet
          2 To be or not to be
          3 Not to ask
          4 <null>

procedure split_words

pascal

procedure split_words(
    text      string    -- text to explore
)returns(
    number    integer   -- word order number  
  , word      string    -- standalone word 
);

split_words picks up standalone words. It treats any digit, english (latin) or russian cyrillic letter as a word symbol. It is equivalent find ( .., '[0-9A-Za-zА-Яа-яЁё]+', , ) except a bit faster.

pascal

text : 'To be or not to be?'

pascal

task : extract all the words.

pascal

solution:
    select
        *
      from
        regex.split_words( :text )

pascal

result:    
     NUMBER SPLIT_WORDS
   ======== ===================
          1 To
          2 be
          3 or
          4 not
          5 to
          6 be

Limitations

Regular expression syntax is defined by PCRE 8.45.

No limits for strings length and result set volume.

Supports all the string types - char(), varchar() and blob sub_type text of any allowed length for this types.

Supports 2 character sets: UTF8 (1-4 bytes/symbol) and WIN1251 (1-byte russian cyrillic charset for Windows).

Installation

Download a release package.
Copy fb_regex.dll to %firebird%\plugins\udr where %firebird% is Firebird (>=3.0) server root directory. Make sure library module matches the Firebird bitness.
Look at script in fb_regex_utf8.sql. You can change all or some varchar() parameters to any length char, varchar or blob sub_type text with character set UTF8 or WIN1251.
Connect to target database and execute the script.

Using

You can use binaries as you see fit.

If you get code or part of code please keep my name and a link to here.

Live chat for Free Pascal, Lazarus, Delphi and every Pascal programmer.

Ffb_regex

Member rating

README

fb_regex

Basis

Routines

procedure matches

procedure groups

procedure find

function find_first

function replace

procedure substitute

procedure split

procedure split_words

Limitations

Installation

Using

fb_regex