Going back to your original query this would work, because it is a boolean test (but it is probably solving a different problem to the one you wanted):
declare @table table(ad_num varchar(20), ad_str1 varchar(20))
select '20', '20 apple avenue' union all
select '20', 'apple avenue'
select * from @table
if (EXISTS(select * from @table where substring(ad_str1, 1, 1) like '[0-9]'))
print 'At least one address starts with a number'
print 'None of the addresses starts with a number'