INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

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

(OP)
Hi.

I have a table with numeric and non numeric:

CODE

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:

CODE

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!

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

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...

CODE

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

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

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

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

SQL Server 2012 and higher:

CODE

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:

CODE

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

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

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

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

How about simply:

SELECT *
FROM @temp
WHERE data1 between '10' and '15'


---- Andy

There is a great need for a sarcasm font.

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

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

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


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

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

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.

CODE

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]' 

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

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

Borislav Borissov
VFP9 SP2, SQL Server

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

4

Quote:

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:

CODE

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:

CODE

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:

CODE

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

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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close