convert varchar to bigint function
I want to create a function that converts a string of characters to bigint. If the conversion is not possible the function should return null. I want the function to work for normal representation (example '10000') and mantissa-exponent representation ('1e1+10') Here is what I have written so far: 

ALTER FUNCTION [dbo].[udf_get_bigint]    
(  
 @character varchar(100)  
)  
RETURNS bigint  

AS  
BEGIN  
   
	if ISNUMERIC(@character)=0 return null
	if LEN(ltrim(rtrim(@character)))>25 return null
	declare @nr numeric(25,4)
	if charindex('e',lower(@character))>0   
	begin
	    declare @real real
	    
	    **set @nr=CONVERT(real,@character)**
	    if 	@nr not between convert(numeric(25),-9223372036854775808) and 
               convert(numeric(25),9223372036854775807) 
				return null
	    set @real = convert(real, @nr)
	    return convert(bigint,convert(numeric(25),@real))
		
	end
	else
		set @nr=CONVERT(numeric(25,4),@character)
        if 	@nr between convert(numeric(25),-9223372036854775808) and 
          convert(numeric(25),9223372036854775807) return convert(bigint,@nr)
                 return null
END  
Now the only problem appears when I need to deal with overflows for mantissa exponent representation. The bolded conversion falls in case of overflow; but what I want it to do is to return null. 
How can I put some preconditions on that conversion so that it does not fall anymore.


call example : select dbo.udf_get_bigint('3e0210')
output: Arithmetic overflow error converting expression to data type real.
convert varchar to bigint function
===

I want to create a function that converts a string of characters to bigint. If the conversion is not possible the function should return null. I want the function to work for normal representation (example '10000') and mantissa-exponent representation ('1e1+10') Here is what I have written so far: 

ALTER FUNCTION [dbo].[udf_get_bigint]    
(  
 @character varchar(100)  
)  
RETURNS bigint  

AS  
BEGIN  
   
	if ISNUMERIC(@character)=0 return null
	if LEN(ltrim(rtrim(@character)))>25 return null
	declare @nr numeric(25,4)
	if charindex('e',lower(@character))>0   
	begin
	    declare @real real
	    
	    **set @nr=CONVERT(real,@character)**
	    if 	@nr not between convert(numeric(25),-9223372036854775808) and 
               convert(numeric(25),9223372036854775807) 
				return null
	    set @real = convert(real, @nr)
	    return convert(bigint,convert(numeric(25),@real))
		
	end
	else
		set @nr=CONVERT(numeric(25,4),@character)
        if 	@nr between convert(numeric(25),-9223372036854775808) and 
          convert(numeric(25),9223372036854775807) return convert(bigint,@nr)
                 return null
END  
Now the only problem appears when I need to deal with overflows for mantissa exponent representation. The bolded conversion falls in case of overflow; but what I want it to do is to return null. 
How can I put some preconditions on that conversion so that it does not fall anymore.


call example : select dbo.udf_get_bigint('3e0210')
output: Arithmetic overflow error converting expression to data type real.