2

I have a column of varchar(50) that I want to convert to int using this line of code:

ALTER TABLE [Table].[dbo].[example]
ALTER COLUMN XYZ int

I eyeballed the contents of column XYZ and they look like integers that never exceed 9999.

I'm familiar with the debugger in Visual Studio and know how to step through code. However, when I try to use the SQL Server Mgmt. Studio debugger, I do not know how to zero in on and fix the field that throws this error:

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

The query runs for about 1 minute, seeming to work, but then the above error pops up.

Taryn
  • 242,637
  • 56
  • 362
  • 405
MrPatterns
  • 4,184
  • 27
  • 65
  • 85

3 Answers3

4

You could use a regular expression to determine which fields will not convert properly.

SELECT * 
FROM example 
WHERE xyz LIKE '%[^0-9]%'

The records return are the one that will not CAST properly when you ALTER your table. Decimals stored in varchar fields will not cast to ints either.

Consider:

select CAST('1.1' as int) -- this returns an error.

Good luck.

sgeddes
  • 62,311
  • 6
  • 61
  • 83
  • Hi, when I run this I get 2 rows returned, and the rows simply replicate the column headers. This is confusing to me, as I don't know how to interpret this – MrPatterns Feb 04 '13 at 17:59
  • What do you mean by replicate column headers? The XYZ column has data as 'XYZ'? – sgeddes Feb 04 '13 at 18:01
  • Exactly. Row 1, in XYZ column it says XYZ. Row, in XYZ column, it says XYZ. – MrPatterns Feb 04 '13 at 18:06
  • Then that's your problem :) -- Update those to some value -- either NULL or '' presumably. You cannot convert the varchar value 'XYZ' to an integer. – sgeddes Feb 04 '13 at 18:07
1

You an use IsNumeric() to find the rows that do not have an int value in the column:

select *
from example
where isnumeric(xyz) <> 1

See SQL Fiddle with Demo

You can also, use a CASE statement:

select 
  case 
    when isnumeric(xyz) <> 1 
    then xyz end
from example
where isnumeric(xyz) <> 1

See SQL Fiddle with Demo

If you want to find records that might contains a period so they are not int values, then you can use:

select  xyz
from example
where isnumeric(xyz) <> 1
  or charindex('.', xyz) <> 0

See SQL Fiddle with Demo. Once you identify these records, you can determine how to proceed with the ALTER TABLE

Taryn
  • 242,637
  • 56
  • 362
  • 405
  • This yields all the rows in which column XYZ is simply empty, or at least appears to be. If it's empty and I attempt to convert to int, is that what causes this failure to convert error? How do I avoid this from happening? – MrPatterns Feb 04 '13 at 17:41
  • @phan Is it an empty string or it is a blank space? – Taryn Feb 04 '13 at 17:43
  • I can't tell the difference. With my eyeballs I simply see an empty cell. How can I tell the difference? – MrPatterns Feb 04 '13 at 17:44
  • @phan Use `len(xyz)` is zero then is it empty if greater than zero than is has blank spaces – Taryn Feb 04 '13 at 17:45
  • Ok, will do that now. Ultimately, how do I take both of these situations and make it so that it doesn't throw an error in my conversions from varchar(50) to int? – MrPatterns Feb 04 '13 at 17:46
  • @bluefeet -- see this: http://sqlfiddle.com/#!3/d41d8/8445 -- your above query returns decimals which won't convert when OP runs ALTER. blanks and nulls will convert -- my guess is a value might have a decimal... – sgeddes Feb 04 '13 at 17:49
  • @sgeddes I included a version that will check for `.`. – Taryn Feb 04 '13 at 18:04
  • @Bluefeet When I added where isnumeric(XYZ) <> 1 AND LEN(XYZ) = 0, I get 2 less results/rows than isnumeric(XYZ) <> 1. This is weird because it seems somehow related to the 2 results that I get from running sgeddes' query. – MrPatterns Feb 04 '13 at 18:12
1

The only way to insure the value can be cast is to use a Try_Cast/Try_Parse function in SQL Server 2012 or a Try Catch pattern in earlier SQL server versions.

The Isnumeric function is of almost no value here because, as you can see, if you run the statement below it will return 1 (true) for the value shown...... and yet the value can not be cast or converted to any numeric data type.

Using the regular expression "NOT LIKE '%[^0-9]%' " to test your value will eliminate the issues with non-numeric characters in the string. However, it will not guarantee that the value can be cast to a given data type. For example "999999999" is a valid integer yet it will cause an overflow error if you cast it to a smallint.

Ex1
Select Isnumeric('$123,456.00')
--Select Cast( '$123,456.00' as int)
--Select Cast( '$123,456.00' as numeric(6,2))
--Select Cast( '$123,456.00' as decimal(6,2))
--Select Convert(int, '$123,456.00')
--Select Convert(numeric(6,2), '$123,456.00')
Select Convert(decimal(6,2), '$123,456.00')
New Alexandria
  • 6,951
  • 4
  • 57
  • 77
Steve
  • 11
  • 1