Convert nchar to int - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Convert nchar to int

Author

Topic

julio
Starting Member

7 Posts

Posted - 2010-10-27 : 12:46:11

Hi!

I've two nchar(10) columns in my DB.

These columns have numbers, like 24,7...

Then I run this code:

select (cast(replace(<column1>,',','') as numeric(10,0))/10)/
(cast(replace(<column2>,',','') as numeric(10,0))/10)*1000
from <DatabaseName>

The result is OK!

But when I run this code:

update <DatabaseName>
update column3=(cast(replace(<column1>,',','') as numeric(10,0))/10)/
(cast(replace(<column2>,',','') as numeric(10,0))/10)*1000

It gives me the error:
Msg 8115, Level 16, State 2, Procedure gramagem, Line 16
Arithmetic overflow error converting expression to data type nvarchar.
The statement has been terminated.

Any suggestion?
The column3 type is Varchar(50)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-10-27 : 13:56:42

I'd guess that you need to CAST that entire expression to a VARCHAR(50) before you try to assign it to column3.

julio
Starting Member

7 Posts

Posted - 2010-10-28 : 09:44:28

Doesn't Work gives the same error.

Any select that I build gives what I pretend, but when update it gives me error.

The goal is to conver a nchar(10) column in a decimal or numeric...

I can´t see wjats the problem!

Appreciate any help.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-28 : 10:22:05

Please give example values so we can try it with declared variables.

No, you're never too old to Yak'n'Roll if you're too young to die.

julio
Starting Member

7 Posts

Posted - 2010-10-28 : 10:54:58

I have a populated table with 4 columns:

ID - int
KG - nchar(10)
MTS - nchar(10)
OPER - Decimal(4,2)

Ex:
ID = '903011 '
KG = '28,73 '
MTS = '52,41 '
OPER = null

Now I want to update the column OPER
the formula is:

OPER = KG / MTS * 1000

This update is to be saved in a trigger.

Thanks in Advance.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-28 : 11:15:41

I think your example values in the given formula brings up 548.17782865865292883
and this isn't possible in a decimal (10,0).

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-28 : 11:16:40

Here are the values to play around:


declare @ID int
declare @KG nchar(10)
declare @MTS nchar(10)
declare @OPER Decimal(4,2)


set @ID = 903011
set @KG = '28,73 '
set @MTS = '52,41 '
set @OPER = null

select (cast(replace(@KG,',','') as numeric(10,0))/10)
select (cast(replace(@MTS,',','') as numeric(10,0))/10)

select (cast(replace(@KG,',','') as numeric(10,0))/10) /
       (cast(replace(@MTS,',','') as numeric(10,0))/10)*1000

No, you're never too old to Yak'n'Roll if you're too young to die.

julio
Starting Member

7 Posts

Posted - 2010-10-28 : 11:27:27

Thank you for your answer, but how can I put that on an update
statement?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-28 : 11:39:25

First you have posted that column3 is a varchar(50) --> then it would work!
But now you are posting that OPER (formerly known as column3) is DECIMAL(4,2)!
DECIMAL(4,2) means: biggest value can be 99.99

So what is the real datatype of the target column?

No, you're never too old to Yak'n'Roll if you're too young to die.

julio
Starting Member

7 Posts

Posted - 2010-10-28 : 11:51:16

The column datatype must be a numeric one, because i have to save on it the result of the operation mentionated above.

This column is new. I can create it with any datatype since it can save my result.

The reason because the datatypes changed is because I changed the datatype to try other ways of saving my result.

The last scenario that I post, is the one that I have right now.

Have you understand?

Thanks for your patience, and sorry for my english.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-28 : 12:03:27

quote:
The column datatype must be a numeric one

quote:
This column is new. I can create it with any datatype since it can save my result

Try varchar(255)
Copy, paste and execute this to see it will work:

declare @ID int
declare @KG nchar(10)
declare @MTS nchar(10)
declare @OPER varchar(255)--Decimal(4,2)


set @ID = 903011
set @KG = '28,73 '
set @MTS = '52,41 '
set @OPER = null

set @OPER= 
       (cast(replace(@KG,',','') as numeric(10,0))/10) /
       (cast(replace(@MTS,',','') as numeric(10,0))/10)*1000
       
select @OPER as OPER

No, you're never too old to Yak'n'Roll if you're too young to die.

julio
Starting Member

7 Posts

Posted - 2010-10-28 : 12:17:10

Webfred, Thank you for your rapid answer.

I try it, and it works.

With select it displays the result.

Now, how can I Update all the records, because my table is already polulated.
I tested your script, putting update instead of select and it works with a where clause for the record with these values explained above.

How can I do this for all the records?

Sorry if the question is too obvious, but I can't see how to do it.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-28 : 12:27:08

I think you have a table like this now:
ID - int
KG - nchar(10)
MTS - nchar(10)
OPER - varchar(255) -- This is the new column

and you want to update OPER for each row in your table

If I am not missunderstanding then this will be your statement:


UPDATE <YourTableNameHere>
SET OPER =
       (cast(replace(KG,',','') as numeric(10,0))/10) /
       (cast(replace(MTS,',','') as numeric(10,0))/10)*1000

But later If you want to do that in a trigger for INSERT and UPDATE


UPDATE t
SET OPER =
       (cast(replace(i.KG,',','') as numeric(10,0))/10) /
       (cast(replace(i.MTS,',','') as numeric(10,0))/10)*1000
FROM <YourTableNameHere> as t
JOIN inserted as i ON i.ID = t.ID

No, you're never too old to Yak'n'Roll if you're too young to die.

julio
Starting Member

7 Posts

Posted - 2010-10-28 : 12:31:49

I've tested and it works.

Once more, Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources