Convertion functions in BODS with Examples_Part3

[former_member190054]

Conversion functions in BODS_Part3

In this section I will explain remaining conversion functions available in BO data services. These functions are mainly.

1.    To_decimal
2.    To_decimal_ext
3.    Varchar_to_long
4.    Long_to_varchar.

TO_DECIMAL

This function will convert a string value into decimal value based on the value given.

The syntax for this function is given below.

To_decimal(Input string,decimal_sep,thousand_sep,scale)

Input string	Input number string
Decimal_sep	Separate decimal component from whole number component
Thousand_sep	Separates thousands from hundreds.
Scale	Number of digit right to the decimal value to be return.

If the input string is invalid then the output this function will be zero.

E.g. To_decimal(’19,130.234’,’.’,’,’,3)=19130.234

TO_DECIMAL_EXT

This function will convert a string value into decimal and include precision as a parameter. It will support decimal data type up to 96 precision.

The syntax for this function is given below.

To_decimal_ext(Input string,decimal_sep,thousand_sep,precision,scale)

Input string	Input number string
Decimal_sep	Separate decimal component from whole number component
Thousand_sep	Separates thousands from hundreds.
Precision	Total number of digit in return value.
Scale	Number of digit right to the decimal value to be return.

If the input string is invalid then the output this function will be zero.

E.g. To_decimal(’19,130.234’,’.’,’,’,38,3)=19130.234

VARCHAR_TO_LONG

This function will convert a string into long data type column.

This function help full when the string size is too high, consider an example if the string having size 25000 and this column mapped to target column having data type varchar(max). In bods the varchar(max) is  read as long data type. So for loading this data this function is very helpful.

Varchar_to_long(column name).

LONG_TO_VARCHAR

This function will convert a data type column value from long to varchar.

The syntax for this function will be.

Long_to_varchar(column_name,max_size,Start_pos)

column_name	Long Column to be converted into varchar
max_size	Max size of the string after conversion
Start_pos	Start position from that position the conversion start. Default value is 1 and if it is negative that means starting position counted backward

Consider the data type of the column in database is varchar(max) ,when importing this column in to data service ,the dataservice will read this column as long data type.So to apply some string  function to this column convert it to varchar by using long_varchar function.

Regards

Asgar