CData Sync supports both extract-transform-load (ETL) and extract-load-transform (ELT) transformations for data pipelines. A built-in transformation library allows organizations to easily mask personally identifiable information, handle NULL values, and more. In this article, we describe the pre-defined transformations available and explain how to apply an in-flight transformation to a column. 

Pre-built SQL transformations 

CData Sync includes a library pre-defined SQL transformations to make in-flight transformations of your data easier than ever. 

MASK(expression, mask character e, start_index e,end_index]]) 

Replaces the characters between start_index and end_index with the mask_character within the string. 

• string_expression: The string expression to be searched.  
• mask_character: The character to mask with.  
• start_index: The optional number of characters to leave unmasked at beginning of string. Defaults to 0.  
• end_index: The optional number of characters to leave unmasked at end of string. Defaults to 0. 

SELECT MASK('1234567890','*',); 

-- Result: '**********' 

SELECT MASK('1234567890','*', 4); 

-- Result: '1234******' 

SELECT MASK('1234567890','*', 4, 2); 

-- Result: '1234****90' 

TRIM(trimspec trimchar FROM string_value) 

Returns the character expression with leading and/or trailing blanks removed. 

• trimspec: Optional. If included must be one of the keywords BOTH, LEADING or TRAILING. 
• trimchar: Optional. If included should be a one-character string value. 
• string_value: The string value to trim.  

SELECT TRIM(' trimmed '); 

-- Result: 'trimmed' 

SELECT TRIM(LEADING FROM ' trimmed '); 

-- Result: 'trimmed ' 

SELECT TRIM('-' FROM '-----trimmed-----'); 

-- Result: 'trimmed' 

SELECT TRIM(BOTH '-' FROM '-----trimmed-----'); 

-- Result: 'trimmed' 

SELECT TRIM(TRAILING '-' FROM '-----trimmed-----'); 

-- Result: '-----trimmed'

ISNULL ( check_expression , replacement_value ) 

Replaces null with the specified replacement value. 

• check_expression: The expression to be checked for null. 
• replacement_value: The expression to be returned if check_expression is null.  

SELECT ISNULL(42, 'Was NULL'); 

-- Result: 42 

 SELECT ISNULL(NULL, 'Was NULL'); 

-- Result: 'Was NULL' 

UPPER ( character_expression ) 

Returns the character expression with lowercase character data converted to uppercase. 

• character_expression: The character expression.  

SELECT UPPER('MIXED case'); 

-- Result: 'MIXED CASE' 

LOWER ( character_expression ) 

Returns the character expression with the uppercase character data converted to lowercase. 

• character_expression: The character expression.  

SELECT LOWER('MIXED case'); 

-- Result: 'mixed case' 

Applying an in-flight transformation 

After creating a replication job, you can apply transformations, including renaming the destination table, destination columns, and modifying the values before they are loaded into the destination. 

To apply a transformation: 

1. Click on a job from the Jobs page. 

2. On the job detail page, navigate to the Task tab and click on a table. 

3. On the table detail page, navigate to the Columns tab and click Edit Mapping. 

4. Click the Options menu (…) for a column and select Apply Transformation.
   
5. In the Transform Source window, edit the column directly or apply any of the pre-defined SQL transformations. The example below masks the first 12 characters of the Receipt_ID with an asterisk (*). 
   
6. Click Done. 

Free trial & more information 

Visit our CData Sync page to read more information about CData Sync. To try out the transformation library, download a free 30-day trial! As always, our world-class Support Team is ready to answer any questions you may have.