CODEWITHSUNDEEP
sqlsql-servernumber-formatting
Stanton
Stanton

Reputation: 1374

tSQL - Conversion from varchar to numeric works for all but integer

I have a table with numbers in a varchar(255) field. They're all greater than one and have multiple decimal places. I'd like to convert them to integers. According to every web site I've consulted, including this one on StackOverflow, either of these should work:

SELECT CAST(VarcharCol AS INT) FROM MyTable

SELECT CONVERT(INT, VarcharCol) FROM MyTable

These both work for me for every kind of numeric value but integer - I can convert to float, decimal, etc. just fine, but trying to convert to integer gives me the following error:

Conversion failed when converting the varchar value '7082.7758172' to data type int.

I've worked around the problem by converting to data type Decimal(6,0), which works fine. But just for my education, can anyone tell me why converting to data type int (or integer) gives me an error? Thanks.

Upvotes: 13

Views: 233396

Answers (6)

Amjad Farooq
Amjad Farooq

Reputation: 17

Try this query:

SELECT cast(column_name as type) as col_identifier FROM tableName WHERE 1=1

Before comparing, the cast function will convert varchar type value to integer type.

Upvotes: -1

Hannah Vernon
Hannah Vernon

Reputation: 3472

Converting a varchar value into an int fails when the value includes a decimal point to prevent loss of data.

If you convert to a decimal or float value first, then convert to int, the conversion works.

Either example below will return 7082:

SELECT CONVERT(int, CONVERT(decimal(12,7), '7082.7758172'));
SELECT CAST(CAST('7082.7758172' as float) as int);

Be aware that converting to a float value may result, in rare circumstances, in a loss of precision. I would tend towards using a decimal value, however you'll need to specify precision and scale values that make sense for the varchar data you're converting.

Upvotes: 25

user10130660
user10130660

Reputation: 1

SELECT
  convert(numeric(18,5),Col1), Col2
     FROM DBname.dbo.TableName
         WHERE isnumeric(isnull(Col1,1)) <> 0

Upvotes: -2

Gayathri L
Gayathri L

Reputation: 1487

Try this

declare @v varchar(20)
set @v = 'Number'
select case when isnumeric(@v) = 1 then @v
else @v end

and

declare @v varchar(20)
set @v = '7082.7758172'
select case when isnumeric(@v) = 1 then @v
else convert(numeric(18,0),@v) end

Upvotes: -1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Presumably, you want to convert values before the decimal place to an integer. If so, use case and check for the right format:

SELECT (case when varcharcol not like '%.%' then cast(varcharcol as int)
             else cast(left(varcharcol, chardindex('.', varcharcol) - 1) as int)
        end) IntVal
FROM MyTable;

Upvotes: -1

nimdil
nimdil

Reputation: 1381

Actually whether there are digits or not is irrelevant. The . (dot) is forbidden if you want to cast to int. Dot can't - logically - be part of Integer definition, so even:

select cast ('7.0' as int)
select cast ('7.' as int)

will fail but both are fine for floats.

Upvotes: 1

Related Questions