Regex pattern inside SQL Replace function?

Question

SELECT REPLACE('<strong>100</strong><b>.00 GB', '%^(^-?\d*\.{0,1}\d+$)%', '');

I want to replace any markup between two parts of the number with above regex, but it does not seem to work. I'm not sure if it is regex syntax that's wrong because I tried simpler one such as '%[^0-9]%' just to test but it didn't work either. Does anyone know how can I achieve this?

Answer 1

In a general sense, SQL Server does not support regular expressions and you cannot use them in the native T-SQL code.

You could write a CLR function to do that. See here, for example.

Answer 2

Here is a recursive function I wrote to accomplish this based off of the previous answers.

CREATE FUNCTION dbo.RecursiveReplace
(
    @P_String VARCHAR(MAX),
    @P_Pattern VARCHAR(MAX),
    @P_ReplaceString VARCHAR(MAX),
    @P_ReplaceLength INT = 1
)
RETURNS VARCHAR(MAX)
BEGIN
    DECLARE @Index INT;

    -- Get starting point of pattern
    SET @Index = PATINDEX(@P_Pattern, @P_String);

    IF @Index > 0
    BEGIN
        -- Perform the replace
        SET @P_String = STUFF(@P_String, PATINDEX(@P_Pattern, @P_String), @P_ReplaceLength, @P_ReplaceString);

        -- Recurse
        SET @P_String = dbo.RecursiveReplace(@P_String, @P_Pattern, @P_ReplaceString, @P_ReplaceLength);
    END;

    RETURN @P_String;
END;

Gist