Regex_Replace via ObjectTable

Since the native Regex_Replace function is not available in DataVirtuality, you can use the following script to create a procedure which will perform this function.

Create Virtual Procedure views.RegexReplace (

IN initialString string not null,

IN regex string not null,

IN replacement string not null)

Returns (resultString string)

As

Begin

Select resultString

From ObjectTable (language 'javascript' '

(new java.lang.String(initialString)).replaceAll(regex, replacement)

'Passing

initialString as initialString,

regex as regex,

replacement as replacement

Columns

resultString string 'dv_row')o;

End;;

This procedure can be run with a simple call- statement:

call "views.RegexReplace"(

    "initialString" => 'string_initialString',

    "regex" => 'string_regex',

    "replacement" => 'string_replacement');;

It is also possible to run the procedure as a subselect within another select- statement:

select <Column>, (select * from (call views.RegexReplace (

initialString => "b.<Column>",

regex => 'regex',

replacement => ''))a) from <SourceTable> b ;;

This is a simple example:

call "views.RegexReplace"(

    "initialString" => 'ab12cd',

    "regex" => '[0-9]',

    "replacement" => ' ');;

This is the result:

resultString
ab cd