cast / convert NVARCHAR into INT
-
Need to Insert an NVARCHAR(2) column into an INT column.
INSERT INTO tbl_a (col_A) VALUES SELECT col_B FROM tab_b
col_A is INT; col_B is NVARCHAR(2)
How can I code a cast/convert function to accomodate this?
Also, is there a simple way to account for non-numeric col_B source values in this INSERT statement?
thx in advance
BT -
INSERT INTO tbl_a (col_A) SELECT cast(col_B as int) FROM tab_b where ISNUMERIC(Col_B) = 1
-
I am having a similar problem. The case and convert methods are not working for converting an nvarchar to int column...I have tried many methods but below are some examples of what I have tried:
problem converting nvarchar(column1) to int (column2)
INSERT INTO TABLENAME(colum2) select cast(column1 as int) from Tablename
---column2 is an int
---column1 is nvarchar
update Tablename
set column2=cast(column1 as int)
update Tablename
set column2=convert(int,column1)
with all of the above statements I get the error message: error converting nvarchar value '1' to int.
-
Can you check the ascii value of that character? If it's not 49 then that's where you're problem is.
-
How about something like this....
select ColInt =
case
when isnumeric(fieldvarchar) = 1 then convert(int, fieldvarchar)
else 0
end
from table
**ASCII stupid question, get a stupid ANSI !!!** -
how about else null end???
So that the columns with the real value of 0 doesn't get mixed up with corrupt data?
-
how about "agreed" Remi...it's "almost" Friday & plus you're right! <:-)
**ASCII stupid question, get a stupid ANSI !!!** -
How about it's thursday morning here?? and I'm not working tomorrow anyways
.This is the kind of mistake you must aboid at all cost... if you lose the original data you're screwed.
-
thanks for all the replies. but I was able to use the column as an nvarchar instead of needing to convert to int.
However, I did try:
select ColInt =
case
when isnumeric(fieldvarchar) = 1 then convert(int, fieldvarchar)
else 0
end
from table
it returns 0 for all columns anyway.whether it equal 1 , 2, or 0.
I think the problem resides in the fact that I am importing a nvarchar field from log file using dts using OEM (which I can change to ansi) then I am using substring function to return the 19th character if a certain condition is met else returns 0. So the new nvarchar columns ends up being either: 0, 1, or 2.
the above statment returns all 0. It still will not convert '1' to 1.
So I think it is something with the importing of the data from a log file.
-
DID YOU ACTUALLY DO THIS?
-
Didn't you said that you were not going to work today?
* Noel -
yes noel - but scc.com is fun (for Remi) - not work! He can't keep away even if he tried!
**ASCII stupid question, get a stupid ANSI !!!** -
Yeah, I am not sure if he even "works" any more
* Noel -
Frank made the same observation several times...judging by how much time he spends on this site (log in at midnight and he's there....) and the # of posts each day people have started to wonder if he's actually not a full time employee of scc.com!!!
<;-)
**ASCII stupid question, get a stupid ANSI !!!** -
btw - if you want to see some poor unsuspecting newbie get blasted out by J.C go to the development forum under Yukon....I was watching this post because I knew J.C was browsing the forum as well and as soon as I saw the post I said to myself - "Oops...the poor guy...what bad luck for him...."
**ASCII stupid question, get a stupid ANSI !!!**
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply