title: convert varchar to bigint function
body: <p>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: </p>
  
  <pre><code>ALTER FUNCTION [dbo].[udf_get_bigint]    
  (  
   @character varchar(100)  
  )  
  RETURNS bigint  
  
  AS  
  BEGIN  
  
      if ISNUMERIC(@character)=0 return null
      if LEN(ltrim(rtrim(@character)))&gt;25 return null
      declare @nr numeric(25,4)
      if charindex('e',lower(@character))&gt;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  
  </code></pre>
  
  <p>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.</p>
  
  <p>call example : select dbo.udf_get_bigint('3e0210')
  output: Arithmetic overflow error converting expression to data type real.</p>