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
