Igor Micev (3/16/2014)

---

Hi

This is also a solution:

declare @table table(start varchar(4)

,[end] varchar(4))

insert @table(start, [end])

values ('0001', '0010')

,('1000', '1010')

,('10BN', '10BN')

,('2000', '2100')

declare @testVal smallint

set @testVal = 2010

;with NumericOnly

as

(select

case when ([start] not like '%[^0-9]%' and [end] not like '%[^0-9]%') then cast([start] as smallint) else 0 end [NStart],

case when ([start] not like '%[^0-9]%' and [end] not like '%[^0-9]%') then cast([end] as smallint) else 0 end [NEnd]

from @table

)

select *

from NumericOnly

where @testVal between NStart and NEnd

It's the conversion. In your case your CTE is just a view on the table's data, and then you're trying to make a comparison/filtering with different data type.

In this case the CTE has real cast data.

Regards,

Igor