Task Factory
"I have found Task Factory to be a huge time saver with SSIS development. The SalesForce components alone saved close to two months of work on a major integration project. I highly recommend!"
Alan Rubel
Database Administrator
Joint Commission Resources
Task Factory - Mark Marinovic
"We bought Task Factory primarily for the SCD Type 2 task, as it enabled us to do several things the built-in SSIS SCD task did not do. Task Factory comes highly recommended from me as our ROI was reached in just days."

Mark Marinovic

Task Factory - Urban Outfitters
"We have found that Task Factory tremendously speeds up the development process and improves performance by means of the high-performance components."

Tim Harris

BI Data Engineer

Urban Outfitters Inc.

Task Factory Loews Corporation
"We bought the product mostly because of the company's reputation for the service they provide and the specific need of a secure SFTP task."

Matt Cushing

Application Systems Analyst/Developer

Loews Corporation

Greater New York City Area

Task Factory - Jay Cole
"I purchase Task Factory over a year ago, and let me tell you, IT WORKS FLAWLESSLY. I haven’t had a single problem with it, and I used to HATE having to make changes with the version you’re using. My advice (for what it’s worth) is to pay the money!"

Jay Cole



Buy Now Free Trial Download Compare Editions More Features

Task Factory RegEx Replace Transform

The RegEx Replace Transform allows the user to define a regular expression to either replace (with user defined value) or extract data from a source column

Feature Highlights

  • There are two actions that can be used with this transform.
    1. The first action is to replace regex matched data with a user defined value. This option allows for the use of capture groups to be part of the replace value. For instance if the regular expression was for a phone number it would look like something like “(\d{3})[-\)\.\s]?(\d{3})[-\)\.\s]?(\d{4})”. There are three capture groups in that regular expression and you could use $1, $2, $3 to extract the area code, prefix and line number.
    2. The second action allows the user to define a regular expression to extract data from the source column. There are several options when using this action including extracting capture groups, separating multiple values by a delimiter and providing a “no match” value.
  • The replaced data can be outputted to the same column in the transform or a new output column.
RegEx Replace Transform

RegEx Replace Transform - Step 1
UI Overview

The RegEx Replace Transform is a transform that allows the user to replace or extract data from a source column using regular expressions

UI Overview

The RegEx Replace Transform is a transform that allows the user to replace or extract data from a source column using regular expressions

RegEx Replace Transform - Step 2

Actions

The RegEx Replace Transform has 2 actions that can be used to either replace or extract data from a source column

  1. Replace matching regular expression pattern with user defined value - This action will allow the user to define a regular expression to search column data and replace the data if the regular expression matches.

      Parameters 
        Search RegEx Pattern   Regular expression used to search the source data 
        Replace With   Specify value which  should be replaced with if specified pattern found in the search string. By default its replaced by {Blank}. Certain characters which are hard to enter by keyboard can be entered as escape sequence (e.g. Tab [\t], CarriageRerurn [\r], LineFeed [\n], VerticalTab [\v], SingleQuote [\'], DoubleQuotes [\"], NullCharacter [\0]) 

      Any capture groups can be used in the replacement data by using $NUMBER (e.g. $1). For instance, in this pattern we define three capture groups in this simple phone number pattern: (\d{3})-(\d{3})-(\d{4}). If the phone number was            904-638-5743       then the capture groups would be:

      $1 = 904
      $2 = 638
      $3 = 5743

      For more information, please visit: http://www.regular-expressions.info/brackets.html
        Ignore Case    Specifies whether the regular expression engine should ignore the case of the data being search
        Maximum Replacements   Specify how many occurances you want to replace. Setting this value to -1 will replace all occurances in the input string. Setting this value to 1 would mean only the first instance of the matched data would be replaced
  2. Extract data from input string using regular expression - This action will extract data from a source column using a regular expression

      Parameters 
        Search RegEx Pattern   Regular expression used to search the source data 
         Extract Placeholder   Specify extract place holder (optional) in the format of $NUMBER (e.g. $1). If match is found then value of the specified place holder will be returned. This is useful if you only want to extract parts of the data from the source specified with capture groups. 

      If placeholder is blank them value of whole match will be returned 

      Any capture groups can be used in the data return by using $NUMBER (e.g. $1). For instance, in this pattern we define three capture groups in this simple phone number pattern: (\d{3})-(\d{3})-(\d{4}). If the phone number was            904-638-5743       then the capture groups would be:

      $1 = 904
      $2 = 638
      $3 = 5743

      For more information, please visit: http://www.regular-expressions.info/brackets.html
        Ignore Case    Specifies whether the regular expression engine should ignore the case of the data being search
        No Match Value    Specify the value to be used if a match is not found. Default is an empty string.
        Separator (For Multiple Match)   If multiple matches found for specified pattern then this separator will be used to join all values and single value will be returned. Default Separator is {SPACE}. Certain characters which is hard to enter by keyboard can be entered as escape sequence (e.g. NewLine [\r\n], Tab [\t], CarriageRerurn [\r], LineFeed [\n], VerticalTab [\v], SingleQuote [\'], DoubleQuotes [\"], NullCharacter [\0]) 
        Maximum Match Count    Specify maximum how many maches should be capture. Specify {-1} to perform unlimited matches.

Actions

The RegEx Replace Transform has 2 actions that can be used to either replace or extract data from a source column

  1. Replace matching regular expression pattern with user defined value - This action will allow the user to define a regular expression to search column data and replace the data if the regular expression matches.

      Parameters 
        Search RegEx Pattern   Regular expression used to search the source data 
        Replace With   Specify value which  should be replaced with if specified pattern found in the search string. By default its replaced by {Blank}. Certain characters which are hard to enter by keyboard can be entered as escape sequence (e.g. Tab [\t], CarriageRerurn [\r], LineFeed [\n], VerticalTab [\v], SingleQuote [\'], DoubleQuotes [\"], NullCharacter [\0]) 

      Any capture groups can be used in the replacement data by using $NUMBER (e.g. $1). For instance, in this pattern we define three capture groups in this simple phone number pattern: (\d{3})-(\d{3})-(\d{4}). If the phone number was            904-638-5743       then the capture groups would be:

      $1 = 904
      $2 = 638
      $3 = 5743

      For more information, please visit: http://www.regular-expressions.info/brackets.html
        Ignore Case    Specifies whether the regular expression engine should ignore the case of the data being search
        Maximum Replacements   Specify how many occurances you want to replace. Setting this value to -1 will replace all occurances in the input string. Setting this value to 1 would mean only the first instance of the matched data would be replaced
  2. Extract data from input string using regular expression - This action will extract data from a source column using a regular expression

      Parameters 
        Search RegEx Pattern   Regular expression used to search the source data 
         Extract Placeholder   Specify extract place holder (optional) in the format of $NUMBER (e.g. $1). If match is found then value of the specified place holder will be returned. This is useful if you only want to extract parts of the data from the source specified with capture groups. 

      If placeholder is blank them value of whole match will be returned 

      Any capture groups can be used in the data return by using $NUMBER (e.g. $1). For instance, in this pattern we define three capture groups in this simple phone number pattern: (\d{3})-(\d{3})-(\d{4}). If the phone number was            904-638-5743       then the capture groups would be:

      $1 = 904
      $2 = 638
      $3 = 5743

      For more information, please visit: http://www.regular-expressions.info/brackets.html
        Ignore Case    Specifies whether the regular expression engine should ignore the case of the data being search
        No Match Value    Specify the value to be used if a match is not found. Default is an empty string.
        Separator (For Multiple Match)   If multiple matches found for specified pattern then this separator will be used to join all values and single value will be returned. Default Separator is {SPACE}. Certain characters which is hard to enter by keyboard can be entered as escape sequence (e.g. NewLine [\r\n], Tab [\t], CarriageRerurn [\r], LineFeed [\n], VerticalTab [\v], SingleQuote [\'], DoubleQuotes [\"], NullCharacter [\0]) 
        Maximum Match Count    Specify maximum how many maches should be capture. Specify {-1} to perform unlimited matches.

RegEx Replace Transform - Step 3

Output Actions

The Replace RegEx Transform has the option of either replacing the data from a source column and outputting the replaced data into the same column or creating a new output column.

Output Actions

The Replace RegEx Transform has the option of either replacing the data from a source column and outputting the replaced data into the same column or creating a new output column.

RegEx Replace Transform - Step 4
If "Output the fixed data to a new output column" is chosen, the new output column name will be "inputcolumn_output". For instance, if the source column is "FirstName", the output column will be "FirstName_output".

If "Output the fixed data to a new output column" is chosen, the new output column name will be "inputcolumn_output". For instance, if the source column is "FirstName", the output column will be "FirstName_output".

View Our Suite of Pragmatic Works Products
Copyright 2014 by Pragmatic Works