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.
