Vfrdtyky

I have a long NVARCHAR variable where I need to replace some pattern like this:

DECLARE @data NVARCHAR(200) = 'Hello [PAT1] stackoverflow [PAT2] world [PAT3]'

I need to replace all [PAT%] with a blank space to look like:

'Hello stackoverflow world'

How can I do this using T-SQL in SQL Server 2008?

I was searching in other questions, and I only found this, but it doesn't help me, because I don't need to preserve de original part of the string.

You may use this function for pattern replace. You can test it with this SQL-Fiddle demo to test.

CREATE FUNCTION dbo.PatternReplace

(

   @InputString VARCHAR(4000),

   @Pattern VARCHAR(100),

   @ReplaceText VARCHAR(4000)

)

RETURNS VARCHAR(4000)

AS

BEGIN

   DECLARE @Result VARCHAR(4000) SET @Result = ''

   -- First character in a match

   DECLARE @First INT

   -- Next character to start search on

   DECLARE @Next INT SET @Next = 1

   -- Length of the total string -- 8001 if @InputString is NULL

   DECLARE @Len INT SET @Len = COALESCE(LEN(@InputString), 8001)

   -- End of a pattern

   DECLARE @EndPattern INT



   WHILE (@Next <= @Len) 

   BEGIN

      SET @First = PATINDEX('%' + @Pattern + '%', SUBSTRING(@InputString, @Next, @Len))

      IF COALESCE(@First, 0) = 0 --no match - return

      BEGIN

         SET @Result = @Result + 

            CASE --return NULL, just like REPLACE, if inputs are NULL

               WHEN  @InputString IS NULL

                     OR @Pattern IS NULL

                     OR @ReplaceText IS NULL THEN NULL

               ELSE SUBSTRING(@InputString, @Next, @Len)

            END

         BREAK

      END

      ELSE

      BEGIN

         -- Concatenate characters before the match to the result

         SET @Result = @Result + SUBSTRING(@InputString, @Next, @First - 1)

         SET @Next = @Next + @First - 1



         SET @EndPattern = 1

         -- Find start of end pattern range

         WHILE PATINDEX(@Pattern, SUBSTRING(@InputString, @Next, @EndPattern)) = 0

            SET @EndPattern = @EndPattern + 1

         -- Find end of pattern range

         WHILE PATINDEX(@Pattern, SUBSTRING(@InputString, @Next, @EndPattern)) > 0

               AND @Len >= (@Next + @EndPattern - 1)

            SET @EndPattern = @EndPattern + 1



         --Either at the end of the pattern or @Next + @EndPattern = @Len

         SET @Result = @Result + @ReplaceText

         SET @Next = @Next + @EndPattern - 1

      END

   END

   RETURN(@Result)

END

Resource link.

+1 @Nico
, I needed a function that will remove special charters from a string, so I adjusted your function a little bit to be able to do this:

select dbo.PatReplaceAll('St. Martin`n tr‘an, or – in - the * field007', '[^0-9A-Z ]', '')

--Returns 'St Martinn tran or  in  the  field007'

Here is the function:

CREATE FUNCTION dbo.PatReplaceAll 

(

    @Source     varchar(8000),

    @Pattern    varchar(  50),

    @Replace    varchar( 100)

)

RETURNS varchar(8000)

AS

BEGIN

    if @Source is null or @Pattern is null or @Replace is null

        return null

    if PATINDEX('%' + @Pattern + '%', @Source) = 0

        return @Source

    -- Declare the return variable here

    DECLARE @Result varchar(8000) SET @Result = ''

    -- The remainder of the @Source to work on

    DECLARE @Remainder varchar(8000) SET @Remainder = @Source

    DECLARE @Idx INT



    WHILE (LEN(@Remainder) > 0) 

    BEGIN

        SET @Idx = PATINDEX('%' + @Pattern + '%', @Remainder)

        IF @Idx = 0 --no match - return

            BEGIN

                SET @Result = @Result +  @Remainder

                BREAK

            END

        -- Concatenate characters before the match to the result

        SET @Result = @Result + SUBSTRING(@Remainder, 1, @Idx - 1)

        -- Adjust the remainder

        SET @Remainder = SUBSTRING(@Remainder, @Idx, LEN(@Remainder) + 1 - @Idx)



        SET @Idx = 1

        -- Find the last char of the pattern (aka its length)

        WHILE PATINDEX(@Pattern, SUBSTRING(@Remainder, 1, @Idx)) = 0

            SET @Idx = @Idx + 1

        --remove the pattern from the remainder

        SET @Remainder = SUBSTRING(@Remainder, @Idx + 1, LEN(@Remainder) - @Idx)

        --Add the replace string

        SET @Result = @Result + @Replace

    END

    return @Result

END

GO