Regex_Replace via ObjectTable

This article only applies to Data Virtuality versions < 2.1.16. Later versions have REGEX_REPLACE support implemented natively. Please see the documentation for details.

Since the native Regex_Replace function is not available in Data Virtuality, 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