Как я могу заменить нечетные шаблоны внутри строки?

Я создаю временную процедуру в SQL, потому что у меня есть значение таблицы, которая написана в методе уценки, поэтому она отображается как отображаемый HTML в веб-браузере (уценка на HTML-преобразование).

Строка столбца в настоящее время выглядит следующим образом:

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

В настоящее время я работаю с жирным и курсивом. Это означает (в случае жирного текста) мне нужно будет заменить нечетный N раз образец ** на <b> и даже раз с </b>.
Я видел replace(), но он выполняет замену на всех шаблонах строки.

Итак, как я могу заменить подстроку только в том случае, если она нечетна или только она есть?

Обновление: Некоторые люди задаются вопросом, какие схемы я использую, поэтому просто посмотрите здесь.

Еще один дополнительный, если вы хотите: Гиперссылка стиля уценки на гиперссылку html выглядит не так просто.

Ответ 1

Используя функцию STUFF и простой цикл WHILE:

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

И вы можете использовать его следующим образом:

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

UPDATE:

Это переписывается как 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

И выполнить:

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

SELECT @returnText

Ответ 2

В соответствии с запросом OP я изменил свой более ранний ответ для выполнения в качестве временной хранимой процедуры. Я оставил свой более ранний ответ, так как считаю, что полезно использовать таблицу строк.

Если известно, что таблица Tally (или Numbers) уже существует с по меньшей мере 8000 значениями, то отмеченная часть CTE может быть опущена, а ссылка CTE заменена на имя существующей таблицы Tally.

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

Вызывается следующим образом:

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

Он выводится как результат:

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.

Ответ 3

В этом решении используются методы, описанные Джеффом Моденом в этой статье о проблеме Running Sum в SQL. Это решение является длительным, но, используя Quirky Update в SQL Server по кластерному индексу, обещает быть намного более эффективным в отношении больших наборов данных, чем решения на основе курсора.

Обновить - изменен ниже, чтобы отключить таблицу строк

Предполагая существование таблицы таблиц, созданной таким образом (с не менее чем 8000 строк):

create table dbo.tally (
     N int not null
    ,unique clustered (N desc)
);
go

with 
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
insert dbo.tally(N)
select row_number() over (order by (select null)) from E4;
go

и функция HtmlTagSpotter, определенная следующим образом:

create function dbo.HtmlTagSPotter(
     @pString       varchar(8000)
    ,@pDelimiter    char(2))
returns table with schemabinding as
return
   WITH 
        Delimiter as (
        select len(@pDelimiter)     as Length,
               len(@pDelimiter)-1   as Offset
    ),
    cteTally(N) AS (
        select top (isnull(datalength(@pstring),0)) 
            row_number() over (order by (select null)) 
        from dbo.tally
    ),
    cteStart(N1) AS (--==== Returns starting position of each "delimiter" )
        select 
            t.N 
        from cteTally t cross join Delimiter 
        where substring(@pString, t.N, Delimiter.Length) = @pDelimiter
    ),
    cteValues as (
        select
             ItemNumber = row_number() over(order by N1)
            ,Location   = N1
        from cteStart
    )
    select
         ItemNumber
        ,Location
    from cteValues
go

тогда запуск следующего SQL выполнит требуемую замену. Обратите внимание, что внутреннее соединение в конце предотвращает преобразование любых завершающих "нечетных" тегов:

create table #t( 
     ItemNo         int             not null
    ,Item           varchar(8000)       null
    ,StartLocation  int             not null
    ,EndLocation    int             not null

    ,constraint PK unique clustered (ItemNo,StartLocation desc)
);

with data(i,s) as ( select i,s from (values
        (1,'Questions about **general computing hardware and software** are off-topic **for Stack Overflow.')
       ,(2,'Questions **about **general computing hardware and software** are off-topic **for Stack Overflow.')
          --....,....1....,....2....,....3....,....4....,....5....,....6....,....7....,....8....,....9....,....0
    )data(i,s)
),
tags as (
    select 
         ItemNo         = data.i
        ,Item           = data.s
        ,Location       = f.Location
        ,IsOpen         = cast((TagNumber % 2) as bit)
        ,Occurrence     = TagNumber
    from data
    cross apply dbo.HtmlTagSPotter(data.s,'**') f
)
insert #t(ItemNo,Item,StartLocation,EndLocation)
select 
     data.ItemNo
    ,data.Item
    ,prev.Location
    ,data.Location
from tags data
join tags prev
   on prev.ItemNo       = data.ItemNo
  and prev.Occurrence = data.Occurrence - 1
  and prev.IsOpen     = 1

union all

select 
    i,s,8001,8002
from data
;

declare @ItemNo     int
       ,@ThisStting varchar(8000);

declare @s varchar(8000);
update this
    set @s = this.Item = case when this.StartLocation > 8000
                              then this.Item
                              else stuff(stuff(@s,this.EndLocation,  2,'</b>')
                                                 ,this.StartLocation,2,'<b>')
                         end
from #t this with (tablockx)
option (maxdop 1);

select
    Item
from (
    select 
         Item
        ,ROW_NUMBER() over (partition by ItemNo order by StartLocation) as rn
    from #t
) t
where rn = 1
go

получая:

Item
------------------------------------------------------------------------------------------------------------
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.

Ответ 4

Один из вариантов - использовать регулярное выражение, поскольку он очень легко заменяет такие шаблоны. Функции RegEx не встроены в SQL Server, поэтому вам нужно использовать SQL CLR, скомпилированные вами или из существующей библиотеки.

В этом примере я буду использовать библиотеку SQL # (SQLsharp) (которой я являюсь автором), но доступны функции RegEx в бесплатной версии.

SELECT SQL#.RegEx_Replace
(
   N'Questions about **general computing hardware and software** are off-topic\
for Qaru unless **they** directly involve tools used primarily for\
**programming. You may be able to get help on [Super User]\
(https://superuser.com/about)', -- @ExpressionToValidate
   N'\*\*([^\*]*)\*\*', -- @RegularExpression
   N'<b>$1</b>', -- @Replacement
   -1, -- @Count (-1 = all)
   1, - @StartAt
   'IgnoreCase' -- @RegEx options
);

Вышеупомянутый шаблон \*\*([^\*]*)\*\* просто ищет что-либо, окруженное двойными звездочками. В этом случае вам не нужно беспокоиться о нечетных/четных. Это также означает, что вы не получите плохо сформированный тег <b> -only, если по какой-то причине в строке есть лишний **. Я добавил два дополнительных тестовых примера к исходной строке: полный набор ** вокруг слова they и непревзойденный набор ** непосредственно перед словом programming. Выход:

Questions about <b>general computing hardware and software</b> are off-topicfor Qaru unless <b>they</b> directly involve tools used primarily for **programming. You may be able to get help on [Super User](https://superuser.com/about)

который отображается как:

Вопросы о общем компьютерном оборудовании и программном обеспечении относятся к теме для, если только они не связаны с инструментами, используемыми в основном для ** программирования. Вы можете получить справку Суперпользователя