T-SQL to pull decimal values from a string

Question

Ok so what I am trying to do is pull the decimal values from a string. My issue is that the strings are not uniform. some may be 6.9% or 5.2mg/L and some may have no number values at all. What I would like to do is return just the decimal(or integer) value from the string and if that does not exist then return NULL.

I have tried this function:

CREATE FUNCTION dbo.udf_GetNumeric
(@strAlphaNumeric VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @intAlpha INT
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)
BEGIN
WHILE @intAlpha > 0
BEGIN
SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )
END
END
RETURN ISNULL(@strAlphaNumeric,0)
END

But that only returns the numbers with no decimal place.

Answer 1

An alternative approach is to remove the characters after the string and before the string. The following expression does this:

select val, 
       stuff(stuff(val+'x', patindex('%[0-9][^0-9.]%', val+'x') + 1, len(val), ''
                  ), 1, patindex('%[0-9]%', val) - 1, '')
from (values ('test123 xxx'), ('123.4'), ('123.4yyyyy'), ('tasdf 8.9'), ('asdb'), ('.2345')) as t(val);

The inner stuff() remove the characters after the number. The +'x' handles the problem that occurs when the number is at the end of the string. The first part handles the part before the number.

This does assume that there is only one number in the string. You can check this with a where clause like:

where val not like '%[0-9]%[^0-9.]%[0-9]%'

Answer 2

You just need to add a . (dot) in both PATINDEX expression:

CREATE FUNCTION dbo.Udf_getnumeric (@strAlphaNumeric VARCHAR(256)) 
returns VARCHAR(256) 
AS 
  BEGIN 
      DECLARE @intAlpha INT 

      SET @intAlpha = Patindex('%[^0-9.]%', @strAlphaNumeric) 

      BEGIN 
          WHILE @intAlpha > 0 
            BEGIN 
                SET @strAlphaNumeric = Stuff(@strAlphaNumeric, @intAlpha, 1, '') 
                SET @intAlpha = Patindex('%[^0-9.]%', @strAlphaNumeric) 
            END 
      END 

      RETURN Isnull(@strAlphaNumeric, 0) 
  END