Remove alphanumeric characters from the Varchar columns and then convert to Float

Question

I have a Laboratory-Test table with 120 columns all with datatype varchar (which supposed to be FLOAT) but these columns also contain characters like ^,*,A-Z,a-z, commas, sentences with full stop "." at the end. I am using the following function to keep all the numeric values including ".".

The issue is this . (dot ), if I use @KeepValues as varchar(50) = '%[^0-9]%' then it will remove all the dots (e.g 1.05*L become 105) which is not something I want.

Could you please help me to resolved this would be very helpful or any alternative solution would be great

Create Function [dbo].[RAC]
    (@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin
    Declare @KeepValues as varchar(50) = '%[^0-9.]%'

    While PatIndex(@KeepValues, @Temp) > 0
        Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')

    Return @Temp
End

My T-SQL CASE statement is :

 ,CASE WHEN LTRIM(RTRIM(DBO.RAC([INR]))) NOT IN ('','.') 
       THEN round(AVG(NULLIF(CAST(DBO.RAC([INR]) as FLOAT), 0)), 2) 
  END AS [INR]

Which version of SQL Server are you using? – Mack Dec 05 '13 at 14:02 — Mack, Dec 05 '13 at 14:02
Hi Mack, Im using SQL Server 2012 R2 – arm Dec 05 '13 at 14:21 — arm, Dec 05 '13 at 14:21

score 3 · Accepted Answer · answered Dec 05 '13 at 18:16

Since you have SQL2012, you can take advantage of the TRY_CONVERT() function

CREATE FUNCTION [dbo].[RAC] (@input varchar(max))
RETURNS TABLE AS
RETURN (
  WITH number_list AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) i FROM sys.objects a)
  SELECT TOP 1 TRY_CONVERT(float,LEFT(@input,i)) float_conversion
  FROM number_list
  WHERE i <= LEN(@input) AND TRY_CONVERT(float,LEFT(@input,i)) IS NOT NULL
  ORDER BY i DESC
)
GO

If you have an actual number_list, which is very useful, use that instead.

DECLARE @table TABLE (data varchar(max))
INSERT @table VALUES
('123.124'), 
('123.567 blah.'),
('123.567E10 blah.'),
('blah 45.2')

SELECT *
FROM @table
OUTER APPLY [dbo].[RAC](data) t

Good point about `TRY_CONVERT()`. I have not seen an int list created like that before - neat idea. — Mike, Dec 05 '13 at 18:28
Thanks Anon! I have changed my script agian with TRY_CONVERT which is more relavent than other solution.Thanks again...............cheers — arm, Dec 12 '13 at 14:28

Solomon Rutzky · Answer 2 · 2019-04-29T03:05:48.480

You need a somewhat basic Regular Expression that will allow you to get digits with a single decimal between two sets of digits (or perhaps digits with no decimal at all). This requires using SQLCLR for the RegEx function. You can find numerous examples of those, or you can use the freely available SQLCLR library SQL# (SQLsharp) (which I am the author of, but the function needed to answer this question is in the Free version).

DECLARE @Expression NVARCHAR(100) = N'\d+(\.\d+)?(e[-+]?\d+)?';

SELECT
    SQL#.RegEx_MatchSimple(N'This is a test. Number here 1.05*L.',
          @Expression, 1, 'IgnoreCase') AS [TheNumber],
    CONVERT(FLOAT, SQL#.RegEx_MatchSimple(N'This is a test. Number here 1.05*L.',
          @Expression, 1, 'IgnoreCase')) AS [Float],
    CONVERT(FLOAT, SQL#.RegEx_MatchSimple(N'Another test. New number 1.05e4*L.',
          @Expression, 1, 'IgnoreCase')) AS [Float2],
    CONVERT(FLOAT, SQL#.RegEx_MatchSimple(N'One more test. Yup 1.05e-4*L.',
          @Expression, 1, 'IgnoreCase')) AS [Float3]

/*
Returns:
    TheNumber   Float       Float2      Float3
    1.05        1.05        10500       0.000105
*/

The only issue with the pattern would be if there is another number in the text (you did say there are full sentences) prior to the one that you want. If you are 100% certain that the value you want will always have a decimal, you could use a simpler expression as follows:

\d+\.\d+(e[-+]?\d+)?

The regular expression allows for optional ( e / e+ / e- ) notation.

score 2 · Answer 3 · edited May 23 '17 at 12:16

PATINDEX supports pattern matching, but only for T-SQL patterns and getting a pattern to do what you need may be impossible. It sounds like you will need to use a regular expression for this you will need a CLR user defined function or you can do it using external to SQL Server by writing an app.

The marked answer to this question will help you get what you need.

Here is a copy of the code for ease of reference:

using System;  
using System.Data;  
using System.Text.RegularExpressions;  
using System.Data.SqlClient;  
using System.Data.SqlTypes;  
using Microsoft.SqlServer.Server;  

public partial class UserDefinedFunctions  
{  
    [Microsoft.SqlServer.Server.SqlFunction]  
    public static SqlString StripNonNumeric(SqlString input)  
    {  
        Regex regEx = new Regex(@"\D");  
        return regEx.Replace(input.Value, "");  
    }  
};

Hi Abu, You're most welcome! Remember to mark a correct answer if it solved your problem. — Mack, Dec 06 '13 at 15:08

Remove alphanumeric characters from the Varchar columns and then convert to Float

3 Answers3