T-SQL to pull decimal values from a string

问题:

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.

回答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]%'

回答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

回答3:

Use this function it will return Decimals Numbers also

    CREATE Function udf_ExtractNumber (@String varchar(256))
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @AlphaNumeric varchar(256)
,@Res varchar(256)

SET @AlphaNumeric = @String
SET @Res = NULL

WHILE (PATINDEX('%[0-9]%', @AlphaNumeric) > 0 )
BEGIN
    IF (PATINDEX('%[0-9]%', @AlphaNumeric) >0 AND PATINDEX('%[0-9]%', @AlphaNumeric) < CHARINDEX('.', @AlphaNumeric))
                BEGIN 
                    SET @Res = CONCAT(@Res ,SUBSTRING(@AlphaNumeric, PATINDEX('%[0-9]%', @AlphaNumeric), 1) )
                    SET @AlphaNumeric = RIGHT(@AlphaNumeric,len(@AlphaNumeric)- PATINDEX('%[0-9]%', @AlphaNumeric))
                END
            ELSE IF (CHARINDEX('.', @AlphaNumeric) >0  AND CHARINDEX('.', @AlphaNumeric) < PATINDEX('%[0-9]%', @AlphaNumeric))
                BEGIN 
                    SET @Res = CONCAT(@Res ,SUBSTRING(@AlphaNumeric, CHARINDEX('.', @AlphaNumeric), 1) )
                    SET @AlphaNumeric = RIGHT(@AlphaNumeric,len(@AlphaNumeric)- CHARINDEX('.', @AlphaNumeric))
                END
            ELSE IF (PATINDEX('%[0-9]%', @AlphaNumeric) >0)
                BEGIN 
                    SET @Res = CONCAT(@Res, SUBSTRING(@AlphaNumeric, PATINDEX('%[0-9]%', @AlphaNumeric), 1) )
                    SET @AlphaNumeric = RIGHT(@AlphaNumeric,len(@AlphaNumeric)- PATINDEX('%[0-9]%', @AlphaNumeric))
                END
            ELSE IF (CHARINDEX('.', @AlphaNumeric) >0 )
                BEGIN 
                    SET @Res = CONCAT(@Res,SUBSTRING(@AlphaNumeric, CHARINDEX('.', @AlphaNumeric), 1))
                    SET @AlphaNumeric = RIGHT(@AlphaNumeric,len(@AlphaNumeric)- CHARINDEX('.', @AlphaNumeric))
    END

END
Return @Res
END

SELECT dbo.udf_ExtractNumber ('AD645.23DGD')

回答4:

I think I have the most efficient method as it requires no loops and simplest as I do it all the string manipulation in one short line. Check it out:

SELECT  string,
        CASE
            --If there's a number, then return it
            WHEN PATINDEX('%[0-9]%',string) != 0 
                --Pretty much find the first number and last number, then return that section(It's off by one so I so I add 1 at the end)
                THEN SUBSTRING(string,PATINDEX('%[0-9]%',string),DATALENGTH(string) - PATINDEX('%[0-9]%',REVERSE(string)) + 1)
            --If there are no numbers, return NULL
            ELSE NULL
        END return_int
FROM
(
    SELECT '123456789.3243421341% of mg/L blah blah blah whitespace    ' AS string
    UNION ALL
    SELECT 'No numbers here'
) A

Results:

string                                                      return_int
----------------------------------------------------------- -----------------------------------------------------------
123456789.3243421341% of mg/L blah blah blah whitespace     123456789.3243421341
No numbers here                                             NULL