Get numeric value from string in SQL Server Database

In this article, we learn about SQL Server database functions, and using these functions gets the numeric value from a string value. Most useful functions when we develop applications database like profile related employees code generation, etc. 

SQL Server function for get numeric value from a string:

CREATE FUNCTION GetNumericFromString(@StringValue VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
  DECLARE @NumericValue INT
  SET @NumericValue = PATINDEX('%[^0-9]%', @StringValue)
  BEGIN
    WHILE @NumericValue > 0
    BEGIN
      SET @StringValue = STUFF(@StringValue, @NumericValue, 1, '' )
      SET @NumericValue = PATINDEX('%[^0-9]%', @StringValue )
    END
  END
  RETURN ISNULL(@StringValue,0)
END;

Call function for getting numeric value from a string:

DECLARE @StringValue VARCHAR(256)='EMPZP20';
SELECT dbo.GetNumericFromString(@StringValue);