title: Remove alphanumeric characters from the Varchar columns and then convert to Float
body: <p>I have a Laboratory-Test table with 120 columns all with datatype <code>varchar</code> (which supposed to be <code>FLOAT</code>) but these columns also contain characters like <code>^,*,A-Z,a-z</code>, commas, sentences with full stop "." at the end. I am using the following function to keep all the numeric values including ".".</p>
  
  <p>The issue is this <code>.</code> (dot ), if I use <code>@KeepValues as varchar(50) = '%[^0-9]%'</code> then it will remove all the dots (e.g <code>1.05*L</code> become <code>105</code>) which is not something I want.</p>
  
  <p>Could you please help me to resolved this would be very helpful or any alternative solution would be great</p>
  
  <pre><code>Create Function [dbo].[RAC]
      (@Temp VarChar(1000))
  Returns VarChar(1000)
  AS
  Begin
      Declare @KeepValues as varchar(50) = '%[^0-9.]%'
  
      While PatIndex(@KeepValues, @Temp) &gt; 0
          Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')
  
      Return @Temp
  End
  </code></pre>
  
  <p>My T-SQL <code>CASE</code> statement is :</p>
  
  <pre><code> ,CASE WHEN LTRIM(RTRIM(DBO.RAC([INR]))) NOT IN ('','.') 
         THEN round(AVG(NULLIF(CAST(DBO.RAC([INR]) as FLOAT), 0)), 2) 
    END AS [INR]
  </code></pre>