String to integer conversion -- mid query

[Quimbly]

Hello,

I'm no expert when it comes to SQL, so go easy on me. I'm a novice.

Here's the situation: My VB6 code is constructing an SQL query string which will eventually be passed to my Access DB. There is a field in the DB which is text, but which also contains numeric values. Here are some examples of data strings that might be in this field:

0
5
11
X
N/A


I want to be able to search the purely numeric values in this field using logical numeric operators ( i.e. <, <=, >, >=, and = ), but I don't want other non-purely-numeric values returned.

So, if my query was something like

blah blah blah WHERE fieldA > 3

it would return the record with the 5 in fieldA, and none of the others from the above group, in the result set.

How do I do it?!

Any help is much appeciated.

 

[DreXor]

i think you can use CONVERT() for this

DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!

 

[DreXor]

cept .. in your example with a conversion, 11 would show up in the results as well as 5 for being > 3


DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!

 

[vongrunt]

CONVERT() will happily crash on non-numerics, so you may use ISNUMERIC() to remove alphas. It ain't bullet-proof but should work for that purpose:

select convert(int, fieldA) as convertedFieldA
from blah
where isnumeric(fieldA)=1 and fieldA >3

Note that SQL Server performs Boolean optimization - unlike VB, it won't evaluate fieldA > 3 for records where isnumeric() returns 0.