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
Comments
1 comment
Das ist ja mal genial und super nützlich! Top!
Vielen, vielen Dank,
Bastian
Please sign in to leave a comment.