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