Functions in SQLScript with regular expressions

Welcome to the Brandeis Training website!

You are not logged in or enrolled in this course, so you will not be able to see all of the content. On the left side you will find the table of contents. But there are some free trial lessons there, which you can recognize by the same yellow background and the lock symbol.

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;

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