kiran
kiran

Reputation: 11

How to remove the space between the minus sign and number's in informatica

i have a issue where the there is a amount field which has data like (- 98765.00),minus{spaces]{numbers} ?, i need to remove the space between the minus and the number and get is as (-98765.00), how do i do it in expression transformation.

field datatype is decimal (8,2).

Thanks, Kiran

Upvotes: 0

Views: 1886

Answers (3)

Maciejg
Maciejg

Reputation: 3353

If my understanding is correct, you need to replace both the spaces and the brackets. Here's the expression:

TO_DECIMAL(
    REPLACECHR(0,
        REPLACECHR(0, '(- 98765.00)', ' ', '') -- this part does the space replacement
    , '()', '') -- this part replaces the brackets
)

Upvotes: 0

Jim Macaulay
Jim Macaulay

Reputation: 5155


You can use REG_REPLACE function to replace space
To achieve this you need to follow below steps,

* Create two variable ports
* REG_REPLACE - function requires string column, so you need to convert the decimal column to string column using TO_CHAR function
First variable port(string) - TO_CHAR(column_name)
* In previous port data is converted to string, now convert it again to decimal and apply REG_REPLACE function
Second variable port(decimal) - to_decimal(reg_replace(first_variable_port,'s+',''))
s - determines the white spaces in informatica regular expression

See the below image, enter image description here
same number which you provided is used. Use the same data type and function

Debugger gives the exact result by removing white space in the below image,

enter image description here
May be you have the issue with other transformations which you are passing through. Debug and verify the data once.

Hope you got it, any issues feel free to ask
To have enjoy informatica, have a fun on https://etlinfromatica.wordpress.com/

Upvotes: 0

Samik
Samik

Reputation: 3455

output_port: TO_DECIMAL(REPLACECHR(FALSE,input_port,' ',''))

REPLACECHR replaces the blanks with empty character, essentially removing them. The first argument can be TRUE/FALSE to specify case sensitive or not, but it is not important in this case.

Upvotes: 0

Related Questions