sql - Conversion failed when converting the nvarchar value 'AAAR78509883' to data type int -


i have nvarchar column in 1 of tables have imported access. trying change int. move new table.

the original query:

insert members_exams_answer     select          ua.members_exams_id, ua.exams_questions_id,          ua.members_exams_answers_value, ua.members_exams_answers_timestamp              members_exams me      full join          useranswers1 ua on me.members_exams_username = ua.members_exams_id     full join          exams_questions eq on eq.exams_questions_id = ua.exams_questions_id 

this throws error:

conversion failed when converting nvarchar value 'aaar78509883' data type int.

i have tired:

select convert (int, useranswers1.members_exams_id) useranswers1 

and

select cast(members_exams_id integer) int_members_exams_id useranswers1  

and

select cast (members_exams_id int) useranswers1 

all result in same error

conversion failed when converting nvarchar value 'aaar78509883' data type int.

your problem systemic data has leading alpha characters need strip out (and same number of alpha characters)

in case use substring strip off alphas (this assumes name number of alphabetic characters in each record). or use varchar or nvarchar field instead of int. if number of leading characters varies or if can leading or trailing or other combination, more complex fix can describe on internet.

the other possibility have bad data. in case identify records not numeric , fix them or null value out if cannot fixed. happens when have stored data in incorrect datatype.


Comments

Popular posts from this blog

java - nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet Hibernate+SpringMVC -

sql - Postgresql tables exists, but getting "relation does not exist" when querying -

asp.net mvc - breakpoint on javascript in CSHTML? -