Scripting Guide

9.8.0

Back Next

String Extraction Example

This example shows how to split the value of the NAME source column into the FIRST_NAME and LAST_NAME target columns.

The following figure shows how the source table columns and virtual columns map to the target table:

Oracle does not have a system function to split a string. You must add the following stored function, split_text, to the target database:

create or replace function target_user.split_text ( some_text varchar2, word_pos int) return varchar2
as 
result varchar2(2000) default null;
begin 
select name into result from (
select trim(regexp_substr(some_text,'[^ ]+',1,level)) name, level lv
from dual
CONNECT BY
regexp_substr(some_text,'[^ ]+',1,level) is not null
)
where lv=word_pos;
return result;
end;

The following SQL expressions for Oracle targets call the split_text stored function to get the first and second words from the space-separated value of the NAME column:

get_firstname: split_text(:NEW.NAME, 1)
get_lastname: split_text(:NEW.NAME, 2)

For SQL Server targets, we can split the first and the last name from the space-separated value of the NAME column without calling a stored function:

get_firstname: LEFT(:NEW.FULL_NAME, CHARINDEX(' ', :NEW.FULL_NAME) - 1)
get_lastname: RIGHT(:NEW.FULL_NAME, CHARINDEX(' ', REVERSE(:NEW.FULL_NAME)) - 1)

Rename Saved Search

Table of Contents

Scripting Guide

Scripting Guide

String Extraction Example