Converting negative value

Hi!

I have a large table width values like this:

000024634300
000-37500000
002783868891
000009603857
000-60000000
000001672396
000000195200
000010315112
000017000000

I need to convert the numbers into an integer-type field. Is this
possible with just native sql-commands? I don't want to make an
active-x script because it will be very slow on the large table.

EspenSELECT col,
CASE WHEN col LIKE '%-%' THEN -1 ELSE 1 END*
CAST(REPLACE(col,'-','') AS NUMERIC(12))
FROM SomeTable

The value 2,783,868,891 exceeds the maximum for an INTEGER column so I've
used NUMERIC here. You could also use BIGINT.

--
David Portas
SQL Server MVP
--

Converting negative numbers to positive

Hi All
I have a table with column Col2 with negative and positive numbers. I would
like to query the col2 and make sure all the negative numbers are converted
to positive in the resultset.
eg
Tb1:
Col1 Col2
-- --
a -1
b -2
c 3
select col2 from tb1 where col1 ='a'
Resultset:
Col2
--
1
How can i do this? Thank you in advance.

5 years experience with SQL Server 2000 and SAP BW/SEM> I would
> like to query the col2 and make sure all the negative numbers are
> converted
> to positive in the resultset.
Try:
SELECT ABS(col2)
FROM tb1
WHERE col1 = 'a'
Hope this helps.
Dan Guzman
SQL Server MVP
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:ACE007EE-E918-4CB6-A59C-8318CAC4B059@.microsoft.com...
> Hi All
> I have a table with column Col2 with negative and positive numbers. I
> would
> like to query the col2 and make sure all the negative numbers are
> converted
> to positive in the resultset.
> eg
> Tb1:
> Col1 Col2
> -- --
> a -1
> b -2
> c 3
> select col2 from tb1 where col1 ='a'
> Resultset:
> Col2
> --
> 1
> How can i do this? Thank you in advance.
>
>
>
>
>
>
>
>
>
> 5 years experience with SQL Server 2000 and SAP BW/SEM|||On Wed, 24 May 2006 16:41:02 -0700, MittyKom wrote:

>Hi All
>I have a table with column Col2 with negative and positive numbers. I woul
d
>like to query the col2 and make sure all the negative numbers are converte
d
>to positive in the resultset.
Hi MittyKom,
SELECT ABS(col2)
FROM tbl
Hugo Kornelis, SQL Server MVP|||MittyKom wrote:
> 5 years experience with SQL Server 2000 and SAP BW/SEM
5 years of experience and you don't know how to convert a negative
number into a positive one?|||Perhaps the OP was mostly on the administration side and/or specialized in
some other SQL Server non-development role. One could guess that there
would be a specialized function for this task but it's sometimes difficult
to find answers in the Books Online when you don't know what to look for.
Hope this helps.
Dan Guzman
SQL Server MVP
"Chris Lim" <blackcap80@.hotmail.com> wrote in message
news:1148517832.138537.174900@.i40g2000cwc.googlegroups.com...
> MittyKom wrote:
> 5 years of experience and you don't know how to convert a negative
> number into a positive one?
>|||To Chris Lim
I am new to sql programming my friend. If you cant help keep your mouth
shhhhhhh. OK.... I have been working on sql server admin only. Enjoy your
day.
"Chris Lim" wrote:

> MittyKom wrote:
> 5 years of experience and you don't know how to convert a negative
> number into a positive one?
>|||Thank you Hugo and Dan. It worked.
"Dan Guzman" wrote:

> Try:
> SELECT ABS(col2)
> FROM tb1
> WHERE col1 = 'a'
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
> news:ACE007EE-E918-4CB6-A59C-8318CAC4B059@.microsoft.com...
>
>

Converting negative integer from string to int

I'm having an issue using the data conversion task and trying to convert from a negative integer that is bounded by brackets e.g, (1) for -1. I keep getting an error that the value cannot be converted although I've tried all signed integer types.

Yeah that doesn't surprise me. You'll be able to do this with a Derived Column component. *I think* the following should do it (assuming the column is called yourCol:

(DT_I4)SUBSTRING(yourCol, 2, 1)

-Jamie

|||

If there are positive numbers in that column as well, you may need to place a conditional split before the derived column; and then, as Jamie suggested, use an expression (in a derived column transform) to remove the brackets and to multiply the resulting number by -1 so you get the negative sign.

|||

A Derived Column Tx would be my preferred option, as this could be done in a single transform. There is no need for the split either. The following expression should do the trick. It avoids the need for any conditional processing, are we +ve or -ve type stuff. The only thing I have not catered for is NULLs.

(DT_I4)REPLACE(REPLACE([Column], "(", "-"), ")", "")

|||Thanks Darren, I'm going to give that a try. It's bizarre because I have a DTS in SQL 2000 and it handles this issue without any problem or conversion issues.|||

whatthedilly wrote:

Thanks Darren, I'm going to give that a try. It's bizarre because I have a DTS in SQL 2000 and it handles this issue without any problem or conversion issues.

The fact that DTS does this is not a good thing. It is a very bad thing and SSIS was a conscious effort to move away from DTS's technique of effectively guessing how it should treat values. With SSIS you have to explicitly tell it how to treat values. You are in control. This removes the possibility of the engine making the wrong guess - as DTS was wont to do.

-Jamie