References:

• Removing non-numeric characters
• Combining insert with common table expression

The SQL:

declare @lastIntUsed int 

create table #temp (clean_number varchar(50), IntCol int) 
; with tally as (select top (100) N=ROW_NUMBER() over (order by @@spid) from sys.all_columns),
data as ( 
   select sometable.number, clean_number
   from sometable
   cross apply ( 
      select (select C + '' FROM (select N, SUBSTRING(sometable.number, N, 1) C  
          from tally
          where N<=DATALENGTH(sometable.number)) [1]
      where C between '0' and '9'
      order by N
      for xml path('')) 
   ) p (clean_number)
   where p.clean_number is not null 
) 
insert into #temp (clean_number, IntCol) 
select number, cast(clean_number as int) IntCol from data

set @lastIntUsed = (select MAX(IntCol) from #temp) 
print @lastIntUsed
drop table #temp

I couldn’t find any way to just get the max number from the CTE itself (doesn’t mean there isn’t a way), so I said screw it – I’ll just stick the results into a temp table and then I can get the max number or the min or the max number that is less than some value or whatever I want.