This article describes Transformation functions supported by Visual Importer ETL (all versions).

String Functions

Trim

Trim(String):string
Trims leading and trailing spaces from a string.

TrimLeft

TrimLeft(String):string
Trims leading spaces from a string.

TrimRight

TrimRight(String):string
Trims trailing spaces from a string.

UpperCase

UpperCase(String):string
UpperCase returns a string with the same text as the string passed in, but with all letters converted to Uppercase

LowerCase

LowerCase(String):string
LowerCase returns a string with the same text as the string passed in, but with all letters converted to LowerCase

Replace

Replace(String,OldPattern,NewPattern):string
Replace replaces all occurrences of the OldPattern by NewPattern within the String

SubString

SubString(String,Index,Count):string
SubString returns a substring containing Count characters or elements starting from Index.

RightString

RightString(String,Count):string
RightString returns the trailing characters of String up to a length of Count characters

LeftString

LeftString(String,Count):string
LeftString returns the leading characters of String up to a length of Count characters

AddCharLeft

AddCharLeft(Char,String,Count):string
AddCharLeft returns a string left-padded to Length with characters Char

AddCharRight

AddCharRight(Char,String,Count):string
AddCharRight returns a string right-padded to Length with characters Char

MakeString        

MakeString(Char,Count):string
MakeString returns a string of Count filled with character Char.

DelSpaces

DelSpaces(String):string
DelSpaces returns string with all spaces deleted except one.

"two  spaces"->"two spaces

Delete

Delete(String,Index,Count):String
DelSpaces returns string with count characters deleted starting from an index.

Insert

Insert(Source,S,Index):String
Insert returns string with s string inserted in an index.

ProperCase

ProperCase(String):string
ProperCase returns a string, with the first letter of each word in uppercase and all other letters in lowercase "proper case"->"Proper Case"

Numeric Functions                          

Abs

Abs(Integer):Integer
Abs returns the absolute value of the argument

Round

Round(Float,Integer):Float
Use Round to round Value to a specified power of ten.

The following examples illustrate the use of Round:

Expression       Value

Round(1234567, 3)     1234000 Round(1.234, -2)        1.23 Round(1.235, -2)        1.24 Round(1.245, -2)        1.24

Sign

Sign(Integer):Integer

Use Sign to test the sign of a numeric value.

Sign returns

0 if AValue is zero. 1 if AValue is greater than zero. -1 if AValue is less than zero.

Date Functions                    

Day

Day(Date,Format):Integer.
Use Day to get the day part of a date value. Day('01012003','DDMMYYYY')

Hour

Hour(Date,Format):Integer.
Use Hour to get the hour part of a date value. Hour('01012003','DDMMYYYY')

Minute

Minute(Date,Format):Integer.
Use Minute to get the minute part of a date value. Minute('01012003','DDMMYYYY')

Month

Month(Date,Format):Integer.
Use Month to get the month part of a date value. Month('01012003','DDMMYYYY')

Second

Second(Date,Format):Integer.
Use Second to get the second part of a date value. Second('01012003','DDMMYYYY')

Year

Year(Date,Format):Integer.
Use Year to get the year part of a date value. Year('01012003','DDMMYYYY')

DayS

DayS(Date,Format):String.
Use DayS to get the day part of a date value as string. DayS('01012003','DDMMYYYY')

HourS

HourS(Date,Format):String.
Use HourS to get the hour part of a date value as a string. HourS('01012003','DDMMYYYY')

MinuteS

MinuteS(Date,Format):String.
Use MinuteS to get the minute part of a date value as a string. MinuteS('01012003','DDMMYYYY')

MonthS

MonthS(Date,Format):String.
Use MonthS to get the month part of a date value as a string. MonthS('01012003','DDMMYYYY')

SecondS

SecondS(Date,Format):String.
Use SecondS to get the second part of a date value as a string. SecondS('01012003','DDMMYYYY')

YearS

YearS(Date,Format):String.
Use YearS to get the year part of a date value as string. YearS('01012003','DDMMYYYY')

IncDateS

IncDateS(Date,Format,ChangeType,Increment):String. ChangeType: YEAR,MONTH,WEEK,DAY,HOUR,MINUTE,SECOND Use IncDateS to Increase ChangeType part of a date value by an Increment. IncDateS ('01012003','DDMMYYYY', 'YEAR',1)

DecDateS

DecDateS(Date,Format,ChangeType,Decrement):String. ChangeType: YEAR,MONTH,WEEK,DAY,HOUR,MINUTE,SECOND Use DecDateS to Decrease ChangeType part of a date value by an Decrement. DecDateS ('01012003','DDMMYYYY', 'YEAR',1)

Conversion Functions

IntegerToString

IntegerToString(Integer):String
IntegerToString converts integer value to string value.

NumberToString

NumberToString(Float):String
NumberToString converts float value to string value.

StringToInteger

StringToInteger(String):Integer
StringToInteger converts string value to integer value.

StringToNumber

StringToNumber(String):Float
StringToNumber converts string value to float value.  

Miscellaneous Functions                 

Iif

Iif(expr1==expr2;expr3;expr4)
Iif function returns expr3 or expr4 depending on expr1==expr2

GetSystemVariable

GetSystemVariable('VARIABLENAME'):string 
GetSystemVariable returns value of 'VARIABLENAME'.

Possible values for 'VARIABLENAME' are:

COMPUTERNAME, OSUSERNAME, DBUSERNAME, BLOCKNUMBER, LINENUMBER, RECORDNUMBER, SYSTEM_DATE

 Pos

Pos(Substr,String): Integer
Pos searches for Substr within String and returns an integer value that is the index of the first character of Substr within String. Pos is case-sensitive. If Substr is not found, Pos returns zero.

GetFileHeaderLine

GetFileHeaderLine(LineNumber): String

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

Testimonials

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,
    Director
  • 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