Remove non numeric characters from consideration in select query

[bmacbmac]

Hi. I have a varchar column where I need to find every numeric value under 199. I tried

select * from mytable
where ISNUMERIC(col001 + 'e0')=1
and convert(int, col001) < 199

However, I get the message:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'OI' to data type int.


Is there a way I can have my query only consider the numeric values? I thought the ISNUMERIC would do it, but the 'convert(int' doesn't work.

Thanks!

Brian

 

[fredericofonseca]

isnumeric not really good for this.

have a look at the third post on this link

http://www.sqlservercentral.com/Forums/Topic456023-338-1.aspx

converting a string to a number has lots to be said - for example should the following strings be considered valid numbers in all situations?
"001 001"
"001-001"

and what about "001-" ? most would consider this as a valid number, but convert will fail.

So most common valid numbers would be
trimmed strings of all numbers
trimmed strings of all numbers with a sign to the right or to the left
less common
trimmed strings of all numbers with possible spaces or - or / separating the numbers

the following code ilustrates what I said

select x
,case when isnumeric(rtrim(ltrim(x))) = 1 then 'is number' else 'not valid' end
,patindex( '%[^ ,.+0-9''^-]%',x) as invalid_position
from (select 'OI' as x
union all select '01' 
union all select '   01'
union all select ' 01 '
union all select ' 01 1'
union all select ' 01-'
union all select ' -01'
) t

for a good validation of numbers a function would be the best to isolate code (although it can be done as outer apply joins) and it could use a combination of patindex as per above, and code to move any - or + on the right side to the left side of the string, and to optionally allow and remove spaces and signs separating digits.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[gmmastros]

The problem is that you cannot control the order of the where clause criteria. In this case SQL Server probably determined that the convert to int check would be quicker than the isnumeric check. Therefore, you get the error on the convert before SQL performs the inumeric check.

Try this code:

Declare @MyTable Table(Col001 VarChar(10))

Insert Into @MyTable Values('4')
Insert Into @MyTable Values('200')
Insert Into @MyTable Values('OI')
Insert Into @MyTable Values('001 001')
Insert Into @MyTable Values('001-001')
Insert Into @MyTable Values('-001')
Insert Into @MyTable Values('')
Insert Into @MyTable Values(' ')
Insert Into @MyTable Values(NULL)
Insert Into @MyTable Values('101.7')

-- Only return integer values less then 199
select	* 
from	@mytable
where	convert(int, Case When col001 > '' And ISNUMERIC(col001 + '.0e0')=1 Then col001 End) < 199 

-- only return positive integer values less than 199
select	* 
from	@mytable
where	convert(int, Case When col001 > '' And ISNUMERIC('-' + col001 + '.0e0')=1 Then col001 End) < 199

In this version of your query, the ISNUMERIC check must occur before the convert because you are converting on the basis of the output from isnumeric.

-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