Заменить с помощью шаблона в SQL

Ответ 1

Я считаю, что лучше всего использовать рекурсивную пользовательскую функцию (UDF). Я включил здесь некоторый код, который вы можете использовать для передачи строки, чтобы добиться результатов, которые вы ищете.

CREATE FUNCTION ufn_StripIDsFromBreadcrumb (@cIndex int, @breadcrumb varchar(max), @theString varchar(max))

RETURNS varchar(max)

AS

BEGIN
DECLARE @nextColon int
DECLARE @nextSlash int

SET @nextColon = CHARINDEX(':', @theString, @cIndex)
SET @nextSlash = CHARINDEX('/', @theString, @nextColon)
SET @breadcrumb = @breadcrumb + SUBSTRING(@theString, @nextColon + 1, @nextSlash - @nextColon)

IF @nextSlash != LEN(@theString)

     BEGIN
     exec @breadcrumb = ufn_StripIDsFromBreadcrumb @cIndex =  @nextSlash, @breadcrumb = @breadcrumb, @theString = @theString
     END
RETURN @breadcrumb
END

Затем вы можете выполнить его с помощью:

DECLARE @myString varchar(max)
EXEC @myString = ufn_StripIDsFromBreadcrumb 1, '/', '/ID1:Category1/ID2:Category2/ID3:Category3/'
PRINT @myString

Ответ 2

Это работает для SQL Server 2005 и выше.

create table strings (
  string varchar(1000)
)

insert into strings values( '/ID1:Category1/ID2:Category2/ID3:Category3/' )  
insert into strings values( '/ID4:Category4/ID5:Category5/ID8:Category6/' )  
insert into strings values( '/ID7:Category7/ID8:Category8/ID9:Category9/' )  
go

with  
replace_with_wildcard ( restrung ) as 
( 
  select replace( string, '', '' ) 
  from strings

  union all 

  select  
    replace( restrung, substring( restrung, patindex( '%ID%', restrung ), 4 ), '' ) 
  from replace_with_wildcard 
  where patindex( '%ID%', restrung ) > 0 
) 

select restrung
from replace_with_wildcard 
where charindex( ':', restrung ) = 0
order by restrung

drop table strings 

Ответ 3

Возможно, вы сможете сделать это, используя функцию Split. Следующая функция split основана на существовании таблицы Numbers, которая буквально содержит последовательный список чисел, например:

Create Table dbo.Numbers( Value int not null primary key clustered )
GO
With Nums As
    (
    Select ROW_NUMBER() OVER( Order By o.object_id ) As Num
    From sys.objects as o
        cross join sys.objects as o2
    )
Insert dbo.Numbers( Value )
Select Num
From Nums
Where Num Between 1 And 10000
GO  


Create Function [dbo].[udf_Split] (@DelimitedList nvarchar(max), @Delimiter nvarchar(2) = ',')
Returns @SplitResults TABLE (Position int NOT NULL PRIMARY KEY, Value nvarchar(max))
AS
/*
PURPOSE: to split the @DelimitedList based on the @Delimter
DESIGN NOTES:
    1. In general the contents of the next item is: NextDelimiterPosition - CurrentStartPosition
    2. CurrentStartPosition = 
        CharIndex(@Delimiter, A.list, N.Value)  = Current Delimiter position
        + Len(@Delimiter)                       + The number of delimiter characters 
        + 1                                     + 1 since the text of the item starts after the delimiter
    3. We need to calculate the delimiter length because the LEN function excludes trailing spaces. Thus
        if a delimiter of ", " (a comma followed by a space) is used, the LEN function will return 1.
    4. The DataLength function returns the number of bytes in the string. However, since we're using
        an nvarchar for the delimiter, the number of bytes will double the number of characters.
*/
Begin
    Declare @DelimiterLength int
    Set @DelimiterLength = DataLength(@Delimiter) / 2

    If Left(@DelimitedList, @DelimiterLength) <> @Delimiter
        Set @DelimitedList = @Delimiter + @DelimitedList

    If Right(@DelimitedList, @DelimiterLength) <> @Delimiter
        Set @DelimitedList = @DelimitedList + @Delimiter

    Insert @SplitResults(Position, Value)
    Select CharIndex(@Delimiter, A.list, N.Value) + @DelimiterLength            
        , Substring (
                    A.List
                    , CharIndex(@Delimiter, A.list, N.Value) + @DelimiterLength         
                    , CharIndex(@Delimiter, A.list, N.Value + 1)                            
                        - ( CharIndex(@Delimiter, A.list, N.Value) + @DelimiterLength ) 
                    )
    From dbo.Numbers As N
        Cross Join (Select @DelimitedList As list) As A
    Where N.Value > 0
        And N.Value < LEN(A.list)
        And Substring(A.list, N.Value, @DelimiterLength) = @Delimiter
    Order By N.Value

    Return
End

Затем вы можете запустить такой запрос, где вы разделите префиксы:

Select Table, Substring(S.Value, CharIndex(':', S.Value) + 1, Len(S.Value))
From Table
    Cross Apply dbo.udf_Split(Table.ListColumn, '/') As S

Это даст вам такие значения, как:

Category1
Category2
Category3

Затем вы можете использовать FOR XML PATH для их повторного объединения:

Select Table.PK
    ,   Stuff(  (
                Select '/' + Substring(S.Value, CharIndex(':', S.Value) + 1, Len(S.Value))
                From Table As Table1
                    Cross Apply dbo.udf_Split(Table.ListColumn, '/') As S1
                Where Table1.PK = Table.PK
                Order By S1.Position
                For Xml Path('')
                ), 1, 1, '') As BreadCrumb
From Table

Ответ 4

Для SQL Server 2005+ вы можете получить поддержку регулярного выражения:

• Включение CLR (не требует перезапуска экземпляра)
• Загрузка вашей функциональности CLR (в этом случае замена регулярного выражения)

Используя собственный TSQL, вам нужно будет определить REPLACE для всего, что вы хотите удалить:

SELECT REPLACE(
         REPLACE(
           REPLACE(''/ID1:Category1/ID2:Category2/ID3:Category3/'', 'ID1:', ''),
            'ID2:', ''), 
         'ID3:', '')

Regex или иначе, вы должны быть уверены, что эти шаблоны не отображаются в фактических данных.

Ответ 5

Вы можете использовать SQL CLR. Здесь статья MSDN:

Ответ 6

declare @test1 nvarchar(max)
set @test1='/ID1:Category1/ID2:Category2/ID3:Category3/'
while(CHARINDEX('ID',@test1)<>0)
Begin
select @test1=REPLACE(@test1,SUBSTRING(@test1,CHARINDEX('ID',@test1),CHARINDEX(':',@test1)-
CHARINDEX('ID',@test1)+1),'') 
End
select @test1