Get Numeric Value from String in SQL Server

In SQL Server, there are in-built functions like CharIndex, PatIndex to find the position of Alpha or Numeric or any special character value from the string. Replace and Stuff functions are there to replace some part of the string. But there are no-inbuilt functions to get only Numeric, Alpha or Alpha Numeric values from the string. In this Post, I am sharing User Defined Functions to get these using the in-built functions in SQL Server.

Function to get Only Numeric Values from the string

CREATE FUNCTION dbo.NumericOnly(
    @string VARCHAR(MAX)
)
RETURNS VARCHAR(MAX) AS
BEGIN
    DECLARE @intAlpha INT
    SET @intAlpha = PATINDEX('%[^0-9]%', @string)
    WHILE @intAlpha > 0
    BEGIN
        SET @string = STUFF(@string, @intAlpha, 1, '' )
        SET @intAlpha = PATINDEX('%[^0-9]%', @string)
    END
    RETURN     @string
END
GO
SELECT dbo.NumericOnly('abcd!@#ABCD#$%123%^%^')

Output

Function to get Only Alpha Values from the string

CREATE FUNCTION dbo.AlphaOnly(
    @string VARCHAR(MAX)
)
RETURNS VARCHAR(MAX) AS
BEGIN
    DECLARE @intAlpha INT
    SET @intAlpha = PATINDEX('%[^a-zA-Z]%', @string)
    WHILE @intAlpha > 0
    BEGIN
        SET @string = STUFF(@string, @intAlpha, 1, '' )
        SET @intAlpha = PATINDEX('%[^a-zA-Z]%', @string)
    END
    RETURN     @string
END
GO
SELECT dbo.AlphaOnly('abcd!@#ABCD#$%123%^%^')

Output

Function to get Only AlphaNumeric Values from the string

CREATE FUNCTION dbo.AlphaNumericOnly(
    @string VARCHAR(MAX)
)
RETURNS VARCHAR(MAX) AS
BEGIN
    DECLARE @intAlpha INT
    SET @intAlpha = PATINDEX('%[^a-zA-Z0-9]%', @string)
    WHILE @intAlpha > 0
    BEGIN
        SET @string = STUFF(@string, @intAlpha, 1, '' )
        SET @intAlpha = PATINDEX('%[^a-zA-Z0-9]%', @string)
    END
    RETURN     @string
END
GO
SELECT dbo.AlphaNumericOnly('abcd!@#ABCD#$%123%^%^')

Output