Varchar column containing numeric and alphanumeric - trying to select where values between numbers 4

[bmacbmac]

Hi.

I have a table with numeric and non numeric:

declare @temp table (Data varchar(20))
insert into @temp values (NULL)
insert into @temp values ('10')
insert into @temp values ('T')
insert into @temp values ('11')
insert into @temp values ('5')
insert into @temp values ('P')

I'm trying to find all values between 10 and 15 and getting an error:

select * from @temp
where Data is not null
and ISNUMERIC(Data + 'e0')=1
and convert(int, data) between 10 and 15

I'm getting an error: Msg 245, Level 16, State 1, Line 16
Conversion failed when converting the varchar value 'T' to data type int.

Is there a way I can find only the values between 10 and 15:
10
11


Thanks!

 

[gmmastros]

The problem here is that you don't have any control over the order in which the where clause is applied. In this query, SQL Server must have decided to perform the convert before the isnumeric check.

Generally speaking, it's awesome that SQL Server does this because it's all about making queries faster.

One way you can accomplish this is to rewrite the query so it looks like this...

declare @temp table (Data varchar(20))
insert into @temp values (NULL)
insert into @temp values ('10')
insert into @temp values ('T')
insert into @temp values ('11')
insert into @temp values ('5')
insert into @temp values ('P') 

select	* 
from	@temp
where	Data is not null
		and Case When ISNUMERIC(Data + 'e0')=1
					Then Convert(Int, Data)
					End Between 10 And 15

Note that there is no Else clause for the Case/When statement. Without an Else, NULL will be returned.

-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

 

[DaveInIowa]

Your query works for me if I leave out the + 'e0'.

 

[bborissov]

SQL Server 2012 and higher:

declare @temp table (Data1 varchar(20))
insert into @temp values (NULL)
insert into @temp values ('10')
insert into @temp values ('T')
insert into @temp values ('11')
insert into @temp values ('5')
insert into @temp values ('P') 

SELECT *
FROM @temp
WHERE TRY_CAST([data1] as int) between 10 and 15

But I'm curious if somebody know WHY your code don't work.
Even this don't work:

declare @temp table (Data varchar(20))
insert into @temp values (NULL)
insert into @temp values ('10')
insert into @temp values ('T')
insert into @temp values ('11')
insert into @temp values ('5')
insert into @temp values ('P') 

SELECT *
FROM (select * from @temp
		where ISNUMERIC(Data + 'e0')=1) test --- Records should be filtered here
WHERE convert(int, data) between 10 and 15

Borislav Borissov
VFP9 SP2, SQL Server

 

[gmmastros]

Boris,

My first attempt was basically the same thing, but with a common table expression instead. It doesn't work because SQL server optimizes the whole query, not just parts at a time. Another way that would have worked would have been to use a temp table or table variable. This forces SQL server to consider each part separately.



-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

 

[Andrzejek]

How about simply:
[tt]
SELECT *
FROM @temp
WHERE data1 between '10' and '15'
[/tt]


---- Andy

There is a great need for a sarcasm font.

 

[johnherman]

Agree with Andy. Treating the numbers as character values should work fine.

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity

 

[SkipVought]

That might work fine in this case but using characters rather than numeric values, ‘2’ is greater than ‘10’

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[DaveInIowa]

Could someone explain why exponential notation is being concatenated to the Data value? Also, to Skip's point, you're not comparing numbers, you are comparing string values; so maybe pattern matching would be a safer way to go.

declare @temp table (Data varchar(20))
insert into @temp values (NULL),('10'),('T'),('11'),('5'),('P'),('1X')

SELECT *
  FROM @temp
 WHERE Data LIKE '[1][0-5]'

 

[bborissov]

Because if you have value '1e12' in the table it will be treated as numeric.


Borislav Borissov
VFP9 SP2, SQL Server

 

[gmmastros]

Could someone explain why exponential notation is being concatenated

This is done because the IsNumeric function returns true if the value can be converted to any number type. This includes scientific notation and money.

Ex:

set language us_english

Select ISNUMERIC('$5.43')
Select ISNUMERIC('1e3')

When concatenating e0 to the isnumeric test, the previous 2 tests that initially returned true, now return false.

Example:

set language us_english

Select ISNUMERIC('$5.43')
Select ISNUMERIC('1e3')

Select ISNUMERIC('$5.43' + 'e0')
Select ISNUMERIC('1e3' + 'e0')

There are other "tricks" similar to this.

IsNumeric (Data + '.0e0') will only return true for whole numbers.
IsNumeric ('-' + '.0e0') will only return true for positive integers.

It's worth noting that there is a subtle "flaw" with this trick. ISNUMERIC returns false for empty string, but returns true with the concatenation trick.

Ex:

Select ISNUMERIC('')
Select ISNUMERIC('' + '.0e0')

Therefore, it's best to use:

Select ISNUMERIC(NULLIF(ColumnName, '') + '.0e0')


-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

 

[bmacbmac]

Thanks everyone! GMMastros your advice was just what I needed.