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

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;