Function to Clear Non-Numeric Characters in SQL Server

Hello everyone,

In this article, I will give information about the function that clears non-numeric characters in SQL Server.

In SQL Server, in some cases, we may want to remove parentheses, dashes, etc. on a numeric text.

You can easily do this with the help of the function below.

CREATE FUNCTION RemovingNonNumericCharacters
(
    @Contents NVARCHAR(512)
)
RETURNS NVARCHAR(512)
AS
BEGIN
    DECLARE @Cleanedcontent NVARCHAR(512);
    SELECT @Cleanedcontent = @Contents;
    WHILE PATINDEX('%[^0-9]%', @Cleanedcontent) > 0
    SELECT @Cleanedcontent
        = REPLACE(@Cleanedcontent, SUBSTRING(@Cleanedcontent, PATINDEX('%[^0-9]%', @Cleanedcontent), 1), '');
    RETURN @Cleanedcontent;
END;

--Use of Function

SELECT dbo.RemovingNonNumericCharacters('0(544)-573-25-84');

When you run the code, you will get a result like the one below.

As you can see, non-numeric characters have been cleared.

Good luck to everyone in business and life.