Thanks Chad, but we do not have rights to add any functions to the database because we do not own or manage the database. It is another product our customers use and we are only providing an interface to view data from within our app.

>Tracy,
>
>I did a *very* quick benchmark and found in my test that the first query is faster which makes sense because it only calls the UDF on the rows it has to. Although, even on that one, it's not going to be the fastest thing in the world. ;)
>
>HTH,
>Chad
>
>>Tracy,
>>
>>Here's a function you can add to your database to strip the letters out of the field and return an integer. I've also included two queries that do effectively the same thing. I don't know which would be more efficient in your case (or if either will be acceptable):
>>

>>create function dbo.StripLetters
>>(
>>	@value varchar(10)
>>)
>>returns int
>>as
>>begin
>>	declare @letter char(1)
>>
>>	set @letter = 'A'
>>
>>	while (@letter <> 'Z')
>>	begin
>>		set @value = replace(@value, @letter, '')
>>
>>		set @letter = char(ascii(@letter) + 1)
>>	end
>>
>>	return convert(int, @value)
>>end
>>go
>>create table oMain (Number char(10))
>>
>>insert into oMain values ('1058')
>>insert into oMain values ('1058A')
>>insert into oMain values ('1167')
>>insert into oMain values ('2054B')
>>insert into oMain values ('3001')
>>insert into oMain values ('3000A')
>>insert into oMain values ('22A')
>>
>>select *
>>	from oMain
>>	where case isnumeric(number)
>>			when 1 then convert(int, number)
>>			else dbo.StripLetters(number)
>>		end between 1100 and 3000
>>
>>select *
>>	from oMain
>>	where dbo.StripLetters(number) between 1100 and 3000
>>
>>drop table oMain
>>drop function dbo.StripLetters
>>

>>HTH,
>>Chad
>>>Some results that should not be included were returned:
>>>
>>>111
>>>21
>>>217
>>>210
>>>...