Error converting varchar to int where isnumeric(myfield)=1

[bmacbmac]

Hi,

I have a with the following data:

Col001:
1
5
2E
6
9


I am working on a larger query, but in it's simplest terms I am trying

select convert(int, col001)
from mytable
where isnumeric(col001)=1

I get an error: Conversion failed when converting the varchar value '2D2' to data type int.

I thought if I included the 'where isnumeric(col001)=1' at the end it would disregard the 2D2, but that doesn't seem to be the case. Is there another way I can select convert (int, col001) in a field that has numeric and alphanumeric values?

Thanks!

 

[SkipVought]

Hi,

isnumeric() is a Boolean function, returning TRUE if col001 IS numeric, or FALSE if it's not.

How about

Where col001=1

 

[bmacbmac]

Thanks Skipvought

where Col001=1 also gives me the error: Conversion failed when converting the varchar value '2D' to data type int.

 

[SkipVought]

2D is a string. How can that be an integer?

 

[bmacbmac]

Yes that's my issue. I am trying to select convert(int only those values that are actually integers. That's why I thought isnumeric(myfield)=1 would filter those out and give me results only based on integers.

 

[SkipVought]

Where col001 = "1

 

[bmacbmac]

I'm trying to select convert(int

all integer data in my table , not just "1

 

[bmacbmac]

I was trying to use isnumeric(mycolumn)=1 for integer values vs isnumeric(mycolumn)=0 for non-integer I wasn't looking specifically for the number 1

 

[SkipVought]

Where ISNUMERIC(col001)

As I stated it will return TRUE or FALSE.

 

[gmmastros]

e and d can be used for scientific notation.

Try...

Where IsNumeric(col001 + 'e0') = 1

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

To see what I mean...

Declare @Value [!]Float[/!]

Set @Value = '2d3'

Select @Value

Note that the following code will cause a problem.

Declare @Value [!]int[/!]

Set @Value = '2d3'

Select @Value

IsNumeric will return true if the expression can be converted to ANY number data type.

By adding 'e0' to the end of the expression, it prevents some false positives. 2d3 can be converted to float, but 2d3 + e0 (2d3e0) cannot be converted to any type of number and therefore IsNumeric will return false.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SkipVought]

But there's no power value after the E. It's just 2E, not 2E0!

 

[gmmastros]

Skip,

I don't make the rules... I just try to explain them.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SkipVought]

I got you. It's a tutorial. Prescriptive rather than descriptive of the current situation.

 

[ElEye]

If I understand your request, you simply want to select the records where the value of that column is a number. If so I think this is what you mean:

SELECT [COL001]
FROM [MYTABLE]
WHERE (ISNUMERIC([COL001]) = 1)

No need to convert (or cast) the column in the select.

Dave
[

http://flashspot.com

]

 

[djj55]

How about something like

DECLARE @x VARCHAR(10) = 'a123'
select @x, CONVERT(INT, @x)
where PATINDEX('%[^0-9]%', @x) = 0;

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[Olaf Doschke]

I actually don't know what your problem is, this works out for me:

declare @mytable as table (col001 Char(2));

insert into @mytable values ('1'),('5'),('2E'),('6'),('9');

select convert(int, col001)
from @mytable
where isnumeric(col001)=1;

Is far as I understand the assumption is made isnumeric('2e') is TRUE, and convert(int, '2E') fails. But not so for me.

In my SQL Server version (see below) isnumeric('2E') is FALSE and therefore convert() is not done, your query logic works in general.
Convert(int,'2e')indeed also fails with the reported error.

My assumption now is, ISNUMERIC seems to depend on some setting. In the end you have to find out which strings cause the error, and then adjust the query. Adding 'e0' to every col001 string may be a solution to overcome the problem, as in

select convert(int, col001)
from @mytable
where isnumeric(rtrim(col001)+'e0')=1;

Bye, Olaf.

[pre]My SQL Server Version:
Microsoft SQL Server 2012 (SP1) - 11.0.3153.0 (X64)
Jul 22 2014 15:26:36
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)[/pre]