Converting varchar to decimal

I'm have a varchar(50) field that I want to convert to decimal. These are
two samples:
+000000063451473.38
-000000038818201.42
Logically I want to:
-remove the + sign and leave the - sign
-remove any leading 0s
My desired outcome is:
63451473.38
-38818201.42
How can I reliably do this. Thanks to anyone who could help.Actually this should be down in the frontend after dataretrieval, because
string functions are not that really fast in SQL Server (2000).
DECLARE @.Number2Convert Varchar(50)
SET @.Number2Convert = '-000000063451473.38'
SELECT (CASE LEFT(@.Number2Convert,1) WHEN '+' THEN '' ELSE '-' END) +
CONVERT(VARCHAR(50),CONVERT(DECIMAL(34,2
),RIGHT(@.Number2Convert,LEN(@.Number2
Convert)-1)))
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Terri" <terri@.cybernets.com> schrieb im Newsbeitrag
news:d6389k$9ht$1@.reader2.nmix.net...
> I'm have a varchar(50) field that I want to convert to decimal. These are
> two samples:
> +000000063451473.38
> -000000038818201.42
> Logically I want to:
> -remove the + sign and leave the - sign
> -remove any leading 0s
> My desired outcome is:
> 63451473.38
> -38818201.42
> How can I reliably do this. Thanks to anyone who could help.
>
>|||something like this should do:
select str(your_col,18,2)
from tb
-oj
"Terri" <terri@.cybernets.com> wrote in message
news:d6389k$9ht$1@.reader2.nmix.net...
> I'm have a varchar(50) field that I want to convert to decimal. These are
> two samples:
> +000000063451473.38
> -000000038818201.42
> Logically I want to:
> -remove the + sign and leave the - sign
> -remove any leading 0s
> My desired outcome is:
> 63451473.38
> -38818201.42
> How can I reliably do this. Thanks to anyone who could help.
>
>|||Thanks Jens, I need to calculate with this data before it even will reach
the front end and it will be a once a day process with minimal records so
performance is not critical here.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:eN3i9fAWFHA.2928@.TK2MSFTNGP10.phx.gbl...
> Actually this should be down in the frontend after dataretrieval, because
> string functions are not that really fast in SQL Server (2000).
> DECLARE @.Number2Convert Varchar(50)
> SET @.Number2Convert = '-000000063451473.38'
> SELECT (CASE LEFT(@.Number2Convert,1) WHEN '+' THEN '' ELSE '-' END) +
>
CONVERT(VARCHAR(50),CONVERT(DECIMAL(34,2
),RIGHT(@.Number2Convert,LEN(@.Number2
Convert)-1)))
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Terri" <terri@.cybernets.com> schrieb im Newsbeitrag
> news:d6389k$9ht$1@.reader2.nmix.net...
are
>|||Terri
Use Cast Function.. Example
Select Cast('+000000063451473.38' as Decimal(38,3))
Charly
"Terri" wrote:

> I'm have a varchar(50) field that I want to convert to decimal. These are
> two samples:
> +000000063451473.38
> -000000038818201.42
> Logically I want to:
> -remove the + sign and leave the - sign
> -remove any leading 0s
> My desired outcome is:
> 63451473.38
> -38818201.42
> How can I reliably do this. Thanks to anyone who could help.
>
>

Converting to decimal

Hi all,
OK, I'm sure very simple question, what am I doing wrong.
how do I get the percentage? of 68 / 1812
@.result keeps showing 0.00
Declare @.result varchar(20)
set @.result = cast(round(100 * (68 / 1812) , 2)as numeric(5,2))
print @.result
thanks
GVMake the inner numbers decimals as the calculations happen before the
cast:
Declare @.result varchar(20)
set @.result = cast(round(100 * (68.0 / 1812.0) , 2)as numeric(5,2))
print @.result
or
Declare @.result varchar(20)
set @.result = cast(round(100 * (cast(68 as numeric(5,2)) / cast(1812 as
numeric(6,2))) , 2)as numeric(5,2))
print @.result|||Integer division = Integer result. Try casting or representing one of values
involved in the formula to numeric.
Declare @.result varchar(20)
set @.result = cast(round(100.00 * (68.00 / 1812.00) , 2)as numeric(5,2))
print @.result
AMB
"gv" wrote:

> Hi all,
> OK, I'm sure very simple question, what am I doing wrong.
> how do I get the percentage? of 68 / 1812
> @.result keeps showing 0.00
> Declare @.result varchar(20)
> set @.result = cast(round(100 * (68 / 1812) , 2)as numeric(5,2))
> print @.result
> thanks
> GV
>
>|||thanks
gv
"gv" <viatorg@.musc.edu> wrote in message
news:%23I$MWu9LFHA.3852@.tk2msftngp13.phx.gbl...
> Hi all,
> OK, I'm sure very simple question, what am I doing wrong.
> how do I get the percentage? of 68 / 1812
> @.result keeps showing 0.00
> Declare @.result varchar(20)
> set @.result = cast(round(100 * (68 / 1812) , 2)as numeric(5,2))
> print @.result
> thanks
> GV
>

Converting of Decimal digits to string

Hi .,
I am facing a problem in converting a number with a maximum decimal digit
of Six to a Comma Separated String.
For Ex.
if the number is 123456.36987 , my expected result is 123,456.36987
if the number is 123456789.36 , my expected result is 123,456,789.36
Please give valuable suggestion...
With Regards,
R.RamaKrishnanGo here:
http://msdn.microsoft.com/library/d...br />
2f3o.asp
...and look for "style values for float or real".
ML
http://milambda.blogspot.com/|||Hello,
If you do not need more than 4 decimal places, you can use the
conversion style 1 for a money value. See:
http://msdn.microsoft.com/library/e..._ca-co_2f3o.asp
However, this is usually handled in the front-end and it's probably
easier to specify a format in the application, not on the server-side.
Razvan

Converting nchar to int (or numeric/decimal)

Hi ,

I have a column in my extract table as nchar(3) and in the destination (the same column with diff name ) it is decimal(3,0) .....i tried to use dataconversion transformation.....i even tried to use cast/convert fn's in the SQL Command (which i use in the "Source Transformation" to get the columns from the extract table).

I tried all the ways i can and still i get the same error..:

[OLE DB Source [1]] Error: SSIS Error Code DTS_E_OLEDBERROR.
An OLE DB error has occurred. Error code: 0x80040E07. An OLE
DB record is available. Source: "Microsoft OLE DB Provider
for SQL Server" Hresult: 0x80040E07 Description: "Error
converting data type nvarchar to numeric.".

Can we actually do it...? any help would be appreciated.

thanks

ravi

you have non-numeric data in the source column that fails the conversion. Have you tried to configure the error output of the conversion transformation to re-direct error so the 'bad' rows are sent to a diffrent output where youc can inspect them?|||

Hi,

No ..No....Its Numeric in the source though the data type is nchar(3).

The thing is actually the column in the extract table has records as 110,150,160,170 and so on .....but its data type is nchar(3).The reason is this extract table comes from AS400 and while extracting As400 doesnt allow to send records if the column in the extract table on Sql Server is defined other than nchar...so have to go with nchar or nvarchar.

thanks

ravi

|||Try a derived column to cast the records. (Why not make them integers?)

(DT_I4)[Column]|||

Works fine for me:

Code Snippet

CREATETABLE #temp(test nchar(3))

INSERTINTO #temp SELECT'111'

INSERTINTO #temp SELECT'211'

INSERTINTO #temp SELECT'311'

SELECTSUM(cast(test asint))FROM #temp

DROPTABLE #temp

Adamus

|||

Adamus Turner wrote:

Works fine for me:

Code Snippet

CREATETABLE #temp(test nchar(3))

INSERTINTO #temp SELECT'111'

INSERTINTO #temp SELECT'211'

INSERTINTO #temp SELECT'311'

SELECTSUM(cast(test asint))FROM #temp

DROPTABLE #temp

Adamus

Yeah, but Ravi's source is AS400, not SQL Server. Though, the other option is to stage the data into SQL Server as it is in AS400, and then use a cast statement when pulling records from the staging table as Adamus has illustrated here.|||

Hey phil,

I extract the table from AS400 to extract_table on my extract database which is SQL Server DB , from there when i need to make some transformations then my column on the staging is decimal(3,0)....where as on the extract table it is nchar(3).

I tried similarly by declaring some variables and casting and converting ...it doensnt work..or am i doing some mistake...:

DECLARE @.X nchar(3)

DECLARE @.Y int

SET @.X ='GGG'

SELECT @.X

SET @.Y =Cast(@.X asint)

SELECT @.Y

thanks for all you views/immediate response's...

let me know if i am doing a mistake here..

thanks,

ravi

|||Then Adamus' example should apply.|||

Hi ,

I think i figured what it was i mean i have like around 200,000 records being pulled in to the extract there ....from which were few records which were nonnumeric in the nchar(3) column i.e apart from '100' , '510' ......there were records with 'MVR' which was causing all the problem... guess...

thanks for the quick responses...

thanks..!!

Ravi

|||

Then check isnumeric before the cast and set non-numerics to 0:

Code Snippet

CREATETABLE #temp(test nchar(3))

INSERTINTO #temp SELECT'111'

INSERTINTO #temp SELECT'AAA'

INSERTINTO #temp SELECT'311'

SELECTCASEISNUMERIC(test)WHEN 1 THENCAST(test asint)ELSE 0 END

FROM #temp

DROPTABLE #temp

Adamus

|||Just a tweak. You may want to set them to NULL, versus a 0. NULLs would be excluded from any math/aggregation totals you may be creating, such as an average. Using a 0 would allow that value to be used. So it's up to you.

In SSIS, you can use a conditional split to move numeric records to your destination table, and non-numerics to the trash, if you desire. Or, you can assign new values to the non-numerics. Up to you.