Functions in SQLScript with regular expressions

In SQLScript we can use regular expressions (regex) to search, replace and decompose into strings. They are irreplaceable when working with HANA.

Regex is a formal language for describing sets of strings. There are a few SQLScript Functions who can evaluate this. This includes:

  • LOCATE_REGEXPR() – Search in strings
  • OCCURRENCES_REGEXPR()– Counts the number of strings that match the pattern
  • REPLACE_REGEXPR() – replaces character strings, whereby individual parts of the regex can be reused in the replacement with Group
  • SUBSTR_REGEXPR() – extracts a character string specified with Regex
Vimeo

Mit dem Laden des Videos akzeptieren Sie die Datenschutzerklärung von Vimeo.
Mehr erfahren

Video laden

Example from the video

From the field delivery_id

  • the number between the 2. and 3. Hyphen and
  • the number after the string ‘INT’

are extracted. We use the SQLScript function SUBSTR_REGEXPR for this :

create table contracts (delivery_id nvarchar(30));
insert into contracts values( 'IT1-20190203-1-INT1');
insert into contracts values( 'IT1-20190203-1-INT2');
insert into contracts values( 'IT1-20190203-2-INT10');
insert into contracts values( 'IT1-20190203-2-INT1');
insert into contracts values( 'IT1-20190203-2-INT2');
insert into contracts values( 'IT1-20190203-10-INT4');

select delivery_id, 
       to_int(SUBSTR_REGEXPR( '.*-.*-(.*)-.*' in delivery_id group 1 )) as sub,
       to_int(SUBSTR_REGEXPR( '.*INT(.*)' in delivery_id group 1 )) as sort_by
       from contracts
       order by sub, 
                sort_by;

drop table contracts;

Related Links:

A tutorial on regular expressions:https://regexone.com

Practice environment for regular expressions: http://www.regexe.de/

Pages 174-180 in the book