Look at the MS SQL Server code below and think what it will return

declare @x varchar(10)
set @X='1245-XMCD-GTH'
select Right(substring(@X,charindex('-',@X)+1,charindex('-',@X,charindex('-',@X)+1)-charindex('-',@X)-1),2)

The result is the last two characters of a middle part -‘CD’

Now answer those three questions
  • How long did it take you to figure out the result?
  • What is the chance of writing the wrong code or getting an error?
  • Can you apply the same function to a text file, Oracle table or in fact any table?
Now let’s do the same using Advanced ETL Processor
We would need two transformation functions Splitter and Right
Splitter creates several fields from one using Delimiter and Text Qualifier. In our Example we have no Qualifier and delimiter is '-'
Right transformation function returns N characters of the right part of the string
In our example, we set N to 2.


Next step is to connect objects
  • Source field [REGISTRATION NUMBER] to the splitter
  • Splitter to Right
  • Right to target field [FIELD1]

One of the major benefits of using Advanced ETL Processor that you can see how the data will look like once it is loaded without actually loading it. All we need to do is to press "Magic Button"

Result of transformation

 As you can see Advanced ETL Processor makes it easy to understand what is happening to the data during transformation.

Advanced ETL Processor is designed to automate extracting data from ANY database, transform, validate it and load into ANY database. Typical usage of it would be extract data from Excel File, Validate Date Formats, Sort data, de-duplicate it and load it into the database, run stored procedure or SQL script, once loading is completed. Unlike Oracle SQL loader, BCP, DTS or SSIS Advanced ETL Processor can also add new and update old records based on the primary key.

Direct link, no registration required.
Star InactiveStar InactiveStar InactiveStar InactiveStar Inactive
Swiss banking
Bank Of Oklahoma
Red Cross
Alta Pacific bank
Gas alberta
Royal Brunei
First Oklahoma bank


What customers say about us

  • swissbanking

    I used Advanced ETL Processor in 2 Enterprises for many business processes and Business automation (outside finance department). I did not find any other tool with so many functions and broad flexibility for that Price! If you need support for bugs or solution design you will get it very fast. Best Support I have ever seen.

    Lionel Albrecht
  • iqvia

    IQVIA and DB Software Laboratory (DBSL) partnered in 2010 and have been working in close cooperation ever since. Over this period of time, DBSL software components formed an integral part of a large number of IQVIA applications currently used by over 20 UK NHS Trusts (Hospitals).

    Dmitry Dorsky,
  • xerox

    The product is easy to learn and once a developer understands the ETL way for solving the problem at hand, the developer's productivity will increase. Even our DBAs now uses the ETL software to quickly create solutions instead of SSIS or SQL jobs.

    Daniel Fung
    Solutions Architect

Read ETL Software customers feedback

This site uses cookies. By continuing to browse the site, you are agreeing to our use of cookies