1

I have a field contains characters and numbers in different format like

f v/45.23698,sdfsdf
25.369xzczxc xcvxv
erwer ewrwr 2.36

I want to extract the numbers part with decimal (any number of decimals) omitting any characters in any case and any symbols ! @ # $ % ^ & * ( ) - _ = + / * \ | ? : "

45.23698
25.369
2.36

if there is any errors like

df df , 23.45.67 dfg fdg

I want to return 23.45 only (ignoring the others) or 23.4567 (ignoring the second decimal point) it doesn't matter at all .. the two solutions are acceptable

23.45
23.45678

both are acceptable

Mariam
  • 533
  • 2
  • 12
  • 22

2 Answers2

1

I Found the solution Query to get only numbers from a string with some modifications from me to achieve my goal as follows

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( SUBSTRING(@strAlphaNumeric,1,CHARINDEX('.',@strAlphaNumeric,1)) ,0)     +REPLACE( ISNULL( SUBSTRING(@strAlphaNumeric,CHARINDEX('.',@strAlphaNumeric,1)+1,LEN(@strAlphaNumeric)) ,0) , '.' , '')


END

GO

SELECT  dbo.udf_GetNumeric('621.382.003/? ?')
SELECT  dbo.udf_GetNumeric('f g / 23.2525 gg')
SELECT  dbo.udf_GetNumeric('2.3658 dfg')
SELECT  dbo.udf_GetNumeric('sdfgsg 5.258')
SELECT  dbo.udf_GetNumeric('23.45.67')
SELECT  dbo.udf_GetNumeric(',621.382.003/? ?')
SELECT  dbo.udf_GetNumeric('.f g / 23.2525 gg')
SELECT  dbo.udf_GetNumeric('2.3658dfg')
SELECT  dbo.udf_GetNumeric('sdfgsg5.258')
SELECT  dbo.udf_GetNumeric('.23.45.67')
Mariam
  • 533
  • 2
  • 12
  • 22
0

If your values are separated by spaces (as in your sample data), then a simple method is:

select t.*, s.value
from t cross apply
     string_split(t.field, ' ') s
where try_convert(float, s.value) is not null;

This returns all numbers in the string.

If you want the first number in the string, you can use logic such as:

select t.*, v.str,
       left(v.str, patindex('%[^0-9.]%', v.str + ' ')) as val

This is not perfect -- for instance, it will return strings that have two decimal points -- but it definitely works on your data.

Here is a db<>fiddle.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786