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.
This question is related to
sql
sql-server
number-formatting
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
SELECT
convert(numeric(18,5),Col1), Col2
FROM DBname.dbo.TableName
WHERE isnumeric(isnull(Col1,1)) <> 0
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;
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.
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.
Source: Stackoverflow.com