How about this. Might be a bit long winded though. This will pull out 742.0 of the following strings

'On 742.0 Evergreen Terrace'

'The number is at the end 742.0'

' 742.0 Evergreen Terrace 1234'

It only picks up the first full number it comes across and then exits the loops. Though if your using null values you may have to play with it.

declare @STR varchar(100)

declare @Tmp varchar(1)

declare @numstr varchar(100)

declare @count int

declare @fndnum binary

select @STR=' 742.0 Evergreen Terrace'

select @count=1,@fndnum=0,@numstr=''

while @fndnum=0

begin

select @Tmp=right(left(@str,@count),1)

print @Tmp

select @count=@count+1

if isnumeric(@tmp)=1

while isnumeric(@tmp)=1

begin

select @numstr=@numstr+@tmp

select @Tmp=right(left(@str,@count),1)

print @numstr

select @count=@count+1

select @fndnum=1

end

if @count>len(@str) set @fndnum=1

end

select 'I have found the number of ' + @numstr