Tracy,

One more thing on this. In the case of an address number like 315-3B or just 315-3 which would represent an apartment number. Would you want to test 315 or 3153 against your number range. I think you would want just 315 as the 3 would represent the apartment number rather than the street address number of the apartment complex.

In that light, I think the PATINDEX solution simply strips out the non-numeric data leaving you with 3153, no? So, how about something like this:

select *, case
			when isnumeric(left(number, 10)) = 1 then convert(int, left(number, 10))
			when isnumeric(left(number, 9)) = 1 then convert(int, left(number, 9))
			when isnumeric(left(number, 8)) = 1 then convert(int, left(number, 8))
			when isnumeric(left(number, 7)) = 1 then convert(int, left(number, 7))
			when isnumeric(left(number, 6)) = 1 then convert(int, left(number, 6))
			when isnumeric(left(number, 5)) = 1 then convert(int, left(number, 5))
			when isnumeric(left(number, 4)) = 1 then convert(int, left(number, 4))
			when isnumeric(left(number, 3)) = 1 then convert(int, left(number, 3))
			when isnumeric(left(number, 2)) = 1 then convert(int, left(number, 2))
			when isnumeric(left(number, 1)) = 1 then convert(int, left(number, 1))
			else 0
		end
	from oMain
	where case
			when isnumeric(left(number, 10)) = 1 then convert(int, left(number, 10))
			when isnumeric(left(number, 9)) = 1 then convert(int, left(number, 9))
			when isnumeric(left(number, 8)) = 1 then convert(int, left(number, 8))
			when isnumeric(left(number, 7)) = 1 then convert(int, left(number, 7))
			when isnumeric(left(number, 6)) = 1 then convert(int, left(number, 6))
			when isnumeric(left(number, 5)) = 1 then convert(int, left(number, 5))
			when isnumeric(left(number, 4)) = 1 then convert(int, left(number, 4))
			when isnumeric(left(number, 3)) = 1 then convert(int, left(number, 3))
			when isnumeric(left(number, 2)) = 1 then convert(int, left(number, 2))
			when isnumeric(left(number, 1)) = 1 then convert(int, left(number, 1))
			else 0
		end between 1100 and 3000

This tests the number field from right to left and takes the most information it can that would be deemed as a single number. Granted, this would not pickup numbers that start with a letter, but just something else for you to keep in mind as you get ready to do your conversion.

HTH,
Chad

>I wish I could mark two threads as the solution, because the end resolution seems to be to run your check for any cases where number cannot be converted to INT and then use Boris' select based on the results...
>
>Thank you SO MUCH Sergey and Borislav (and to all who looked at it)!