How I can replace odd patterns inside a string?

Question

I'm in the process of creating a temporary procedure in SQL because I have a value of a table which is written in markdown, so it appear as rendered HTML in the web browser (markdown to HTML conversion).

String of the column currently look like this:

Questions about **general computing hardware and software** are off-topic for Stack Overflow unless they directly involve tools used primarily for programming. You may be able to get help on [Super User](http://superuser.com/about)

I'm currently working with bold and italic text. This mean (in the case of bold text) I will need to replace odd N times the pattern**with<b>and even times with</b>.
I saw replace() but it perform the replacement on all the patterns of the string.

So How I can replace a sub-string only if it is odd or only it is even?

Update: Some peoples wonder what schemas I'm using so just take a look here.

One more extra if you want: The markdown style hyperlink to html hyperlink doesn't look so simple.

Answer 1

Using theSTUFFfunction and a simpleWHILEloop:

CREATE FUNCTION dbo.fn_OddEvenReplace(@text nvarchar(500), 
                                      @textToReplace nvarchar(10), 
                                      @oddText nvarchar(10), 
                                      @evenText nvarchar(500))
RETURNS varchar(max)
AS
BEGIN
    DECLARE @counter tinyint
    SET @counter = 1

    DECLARE @switchText nvarchar(10)
    WHILE CHARINDEX(@textToReplace, @text, 1) > 0
    BEGIN
        SELECT @text = STUFF(@text, 
                    CHARINDEX(@textToReplace, @text, 1), 
                    LEN(@textToReplace), 
                    IIF(@counter%2=0,@evenText,@oddText)),
                @counter = @counter + 1
    END
    RETURN @text
END

And you can use it like this:

SELECT dbo.fn_OddEvenReplace(column, '**', '<b>', '</b>')
FROM table

UPDATE:

This is re-written as an SP:

CREATE PROC dbo.##sp_OddEvenReplace @text nvarchar(500), 
                                  @textToReplace nvarchar(10), 
                                  @oddText nvarchar(10), 
                                  @evenText nvarchar(10),
                                  @returnText nvarchar(500) output
AS
BEGIN
    DECLARE @counter tinyint
    SET @counter = 1

    DECLARE @switchText nvarchar(10)
    WHILE CHARINDEX(@textToReplace, @text, 1) > 0
    BEGIN
        SELECT @text = STUFF(@text, 
                    CHARINDEX(@textToReplace, @text, 1), 
                    LEN(@textToReplace), 
                    IIF(@counter%2=0,@evenText,@oddText)),
                @counter = @counter + 1
    END
    SET @returnText = @text
END
GO

And to execute:

DECLARE @returnText nvarchar(500)
EXEC dbo.##sp_OddEvenReplace '**a** **b** **c**', '**', '<b>', '</b>', @returnText output

SELECT @returnText

Answer 2

As per OP's request I have modified my earlier answer to perform as a temporary stored procedure. I have left my earlier answer as I believe the usage against a table of strings to be useful also.

If a Tally (or Numbers) table is known to already exist with at least 8000 values, then the marked section of the CTE can be omitted and the CTE reference tally replaced with the name of the existing Tally table.

create procedure #HtmlTagExpander(
     @InString   varchar(8000) 
    ,@OutString  varchar(8000)  output
)as 
begin
    declare @Delimiter  char(2) = '**';

    create table #t( 
         StartLocation  int             not null
        ,EndLocation    int             not null

        ,constraint PK unique clustered (StartLocation desc)
    );

    with 
          -- vvv Only needed in absence of Tally table vvv
    E1(N) as ( 
        select 1 from (values
            (1),(1),(1),(1),(1),
            (1),(1),(1),(1),(1)
        ) E1(N)
    ),                                              --10E+1 or 10 rows
    E2(N) as (select 1 from E1 a cross join E1 b),  --10E+2 or 100 rows
    E4(N) As (select 1 from E2 a cross join E2 b),  --10E+4 or 10,000 rows max
    tally(N) as (select row_number() over (order by (select null)) from E4),
          -- ^^^ Only needed in absence of Tally table ^^^

    Delimiter as (
        select len(@Delimiter)     as Length,
               len(@Delimiter)-1   as Offset
    ),
    cteTally(N) AS (
        select top (isnull(datalength(@InString),0)) 
            row_number() over (order by (select null)) 
        from tally
    ),
    cteStart(N1) AS 
        select 
            t.N 
        from cteTally t cross join Delimiter 
        where substring(@InString, t.N, Delimiter.Length) = @Delimiter
    ),
    cteValues as (
        select
             TagNumber = row_number() over(order by N1)
            ,Location   = N1
        from cteStart
    ),
    HtmlTagSpotter as (
        select
             TagNumber
            ,Location
        from cteValues
    ),
    tags as (
        select 
             Location       = f.Location
            ,IsOpen         = cast((TagNumber % 2) as bit)
            ,Occurrence     = TagNumber
        from HtmlTagSpotter f
    )
    insert #t(StartLocation,EndLocation)
    select 
         prev.Location
        ,data.Location
    from tags data
    join tags prev
       on prev.Occurrence = data.Occurrence - 1
      and prev.IsOpen     = 1;

    set @outString = @Instring;

    update this
    set @outString = stuff(stuff(@outString,this.EndLocation,  2,'</b>')
                                           ,this.StartLocation,2,'<b>')
    from #t this with (tablockx)
    option (maxdop 1);
end
go

Invoked like this:

declare @InString   varchar(8000) 
       ,@OutString  varchar(8000);

set @inString = 'Questions about **general computing hardware and software** are off-topic **for Stack Overflow.';
exec #HtmlTagExpander @InString,@OutString out; select @OutString;

set @inString = 'Questions **about** general computing hardware and software **are off-topic** for Stack Overflow.';
exec #HtmlTagExpander @InString,@OutString out; select @OutString;
go

drop procedure #HtmlTagExpander;
go

It yields as output:

Questions about <b>general computing hardware and software</b> are off-topic **for Stack Overflow.

Questions <b>about</b> general computing hardware and software <b>are off-topic</b> for Stack Overflow.