How to replace a value in Bracket with negative value in ssis

Question

How to replace a value in Bracket with negative value in ssis

SQL_Buddy99

Right there with Babe

Points: 776
More actions
June 28, 2013 at 9:52 am

#295748

Hi,
I have imported data into a database from an excel file and some of the columns contain values are like (392.03), (2.25), (65.00). Actually these values are should be -ve values can you guys help me how to convert these into -392.03,-2.25,-65.00

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

SQL_Learning (6/28/2013)
Hi,
I have imported data into a database from an excel file and some of the columns contain values are like (392.03), (2.25), (65.00). Actually these values are should be -ve values can you guys help me how to convert these into -392.03,-2.25,-65.00

So, you've loaded your numeric values into column of string data type.

Now you can convert these "string" values into numeric type using something like that:

CASE WHEN LEFT(YourCharColumn,1) = '(' THEN -1 ELSE 0 END * CAST(REPLACE(REPLACE(YourCharColumn,'(',''),')','') AS DECIMAL(15,2))

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

errolthomasql SSC-Addicted Points: 439 More actions · Answer 2

how do i add that as an expression in the Derived Column Transformation Editor? my data is coming from a csv into a staging table which has numeric and decimal data types where the parenthesis values are. i am not able to add another physical table with different data type.

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 3

What have you done so far to learn how to use the Derived Column Transform?

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

errolthomasql SSC-Addicted Points: 439 More actions · Answer 4

Not enough... I have tried the following in conditional split transformation editor and failed

REPLACE([QuantityUnits],"(","-")

REPLACE(QuantityUnits,')','')

I have tried the following in Derived Column Transformation editor and failed

REPLACE(REPLACE([QuantityUnits],”(“,”-”),”)“,””)

SUBSTRING([QuantityUnits],1,1) == "(" ? REPLACE(REPLACE([QuantityUnits],"(","-"),")","") : [QuantityUnits]

I have tried changing the data type in the flat file connection manager to string data type and that doesnt help

The csv file's problem columns have negative values that are represented like (2.5) and values with over 3 digits represented like " 1,074 ". The quotes are handled by my specification of the text qualifier but I am stuck on converting the negative values.