Conversion failed when converting the vardhar value 'abc' to data type int

0
votes

I am inserting data from one table to another so when inserting I got above error mentioned in title

Insert into dbo.source(
title
)
Select 
Title from dbi.destination

title in dbo.source table is of INT data type and title in dbo.destination table is of Varchar data type and I have data like abc, efg, etc. in the dbo.destination table.

So how to solve this now or is it possible to convert and insert values?

5
sql-server
Tag appropriate database name. - mkRabbani
I don't think it's required, both are in same db only - Vas Z
you cannot convert vachar like abc into an int field - zip
So then how can we insert Null values into that field if we can't convert? @zip - Vas Z
Use when Title not like '%[^0-9]%' to find the non numeric characters - zip

5 Answers

1
votes

You can use SQL Server try_cast() function as shown below. Here is the official documentation of TRY_CAST (Transact-SQL).

It Returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.

Syntax

TRY_CAST ( expression AS data_type [ ( length ) ] )

And the implementation in your query. 

INSERT INTO dbo.source (title)
SELECT try_cast(Title AS INT)
FROM dbi.destination

Using this solution you need to be sure you have set the column allow null true otherwise it will give error.

If you do not want to set the allow null then you need minor changes in select query as shown below - passing the addition criteria to avoid null values.

Select ... from ... where try_cast(Title AS INT) is not null
0
votes

You must use isnumeric method of SQL for checking is data numeric or not

CONVERT(INT,
    CASE
    WHEN IsNumeric(CONVERT(VARCHAR(12), a.value)) = 1 THEN CONVERT(VARCHAR(12),a.value)
    ELSE 0 END)
0
votes

Think about your data types - obviously you cannot have a text string like 'abc' in a column that is defined to hold integers.

It makes no sense to copy a string value into an integer column, so you have to confirm how you want to handle these - do you simply discard them (what is the impact of throwing data away?) or do you replace them with some other value?

If you want to ignore them and use NULL in place then use:

INSERT dbo.Source (Title)
SELECT CASE 
         WHEN ISNUMERIC(Title) = 1 THEN CAST(Title as INT)
         ELSE NULL
       END
FROM dbo.Destination

If you want to replace the value then simply change NULL above to the value you want e.g. 0

0
votes

You can use regex to root out non numeric characters

Insert into dbo.source(
title
)
Select 
case when Title not like '%[^0-9]%' then null else cast(Title  as int) end as Title 
from dbi.destination
0
votes

Just filter only numeric field from destination table like as below:

Insert into dbo.source(
title
)
Select 
Title from dbi.destination
where ISNUMERIC(Title) = 1