Extract numerical data from the string

In the database environment, the embedded numerical data may need to be extracted from the string. Consider the following example:

string 1: 003preliminary Examination Plan String 2: Coordination005 String 3: BALANCE1000SHEET

字符串 1: 003Preliminary Examination Plan   
字符串 2: Coordination005  
字符串 3: Balance1000sheet

string 1: 003 String 2: 005 String 3: 1000

字符串 1: 003
字符串 2: 005
字符串 3: 1000

Create Function dbo.udf_getnumeric (( @Stralphanumeric Varchar (256) Cure Return varchar (256) AS Begin Declare @intalpha int Setalpha = patinDex ('%[^0-9]%', @Stralphanumeric) Begin While @intalpha> 0 Begin Set @Strachanumeric = Stuff (@StralPhanumeric, @InTalpha, 1, '') Setalpha = patinDex ('%[^0-9]%', @Stralphanumeric) End End Return isnull (trim (@stralphanumeric), '0') End GO

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(TRIM(@strAlphaNumeric), '0')
END
GO

select dbo.udf_getNumeric (column_name) From table_name

This will only return the digital part of the specified string in the specified column. For the example data provided, the result will match the expected value:

SELECT dbo.udf_GetNumeric(column_name) 
from table_name

Note:

in the original code

003
005
1000

]. Unnecessary nested and type conversion issues. This correction function is clearer and easier to understand and maintain.